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 ?

mysql> create table FormatNumberTwoDecimalPlace    -> (    -> Number float    -> ); Query OK, 0 rows affected (0.59 sec)

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

mysql> insert into FormatNumberTwoDecimalPlace values(123.456); Query OK, 1 row affected (0.13 sec) mysql> insert into FormatNumberTwoDecimalPlace values(1.6789); Query OK, 1 row affected (0.19 sec) mysql> insert into FormatNumberTwoDecimalPlace values(12.2); Query OK, 1 row affected (0.14 sec) mysql> insert into FormatNumberTwoDecimalPlace values(12356.23145); Query OK, 1 row affected (0.40 sec) mysql> insert into FormatNumberTwoDecimalPlace values(12356); Query OK, 1 row affected (0.14 sec) mysql> insert into FormatNumberTwoDecimalPlace values(.5678); Query OK, 1 row affected (0.28 sec)

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

mysql> select *from FormatNumberTwoDecimalPlace;

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 ?

mysql> select truncate(Number,2) as TwoValueAfterDecimal from FormatNumberTwoDecimalPlace;

Output

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