How to format number to 2 decimal places in MySQL?

You can use TRUNCATE() function from MySQL to format number to 2 decimal places. The syntax is as follows ?

SELECT TRUNCATE(yourColumnName,2) as anyVariableName from yourTableName;

To understand the above syntax, let us first create a table. The query to create a table is as follows ?

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> create table </span><span class="typ">FormatNumberTwoDecimalPlace</span><span class="pln">
   </span><span class="pun">-></span><span class="pln"> </span><span class="pun">(</span><span class="pln">
   </span><span class="pun">-></span><span class="pln"> </span><span class="typ">Number</span><span class="pln"> </span><span class="kwd">float</span><span class="pln">
   </span><span class="pun">-></span><span class="pln"> </span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.59</span><span class="pln"> sec</span><span class="pun">)</span>

Insert some records in the table using insert command. The query is as follows ?

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">FormatNumberTwoDecimalPlace</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">123.456</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.13</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">

mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">FormatNumberTwoDecimalPlace</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">1.6789</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.19</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">

mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">FormatNumberTwoDecimalPlace</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">12.2</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.14</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">

mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">FormatNumberTwoDecimalPlace</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">12356.23145</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.40</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">

mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">FormatNumberTwoDecimalPlace</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">12356</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.14</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">

mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">FormatNumberTwoDecimalPlace</span><span class="pln"> values</span><span class="pun">(.</span><span class="lit">5678</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.28</span><span class="pln"> sec</span><span class="pun">)</span>

Let us now display all records from the table using select command. The query is as follows ?

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">FormatNumberTwoDecimalPlace</span><span class="pun">;</span>

Output

+---------+
| Number  |
+---------+
| 123.456 |
|  1.6789 |
|    12.2 |
| 12356.2 |
|   12356 |
|  0.5678 |
+---------+
6 rows in set (0.04 sec)

Here is the query to format number to two decimal places ?

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> truncate</span><span class="pun">(</span><span class="typ">Number</span><span class="pun">,</span><span class="lit">2</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">TwoValueAfterDecimal</span><span class="pln"> </span><span class="kwd">from</span><span class="pln">
</span><span class="typ">FormatNumberTwoDecimalPlace</span><span class="pun">;</span>

Output

+----------------------+
| TwoValueAfterDecimal |
+----------------------+
|               123.45 |
|                 1.67 |
|                12.19 |
|             12356.23 |
|             12356.00 |
|                 0.56 |
+----------------------+
6 rows in set (0.00 sec)