Format number to 2 decimal places
MysqlFormattingDecimalMysql Problem Overview
I would like to know how can I output a number with 2 decimal places, without rounding the original number.
For example:
2229,999 -> 2229,99
I already tried:
FORMAT(2229.999, 2)
CONVERT(2229.999, DECIMAL(4,2))
Mysql Solutions
Solution 1 - Mysql
When formatting number to 2 decimal places you have two options TRUNCATE and ROUND. You are looking for TRUNCATE function.
Examples:
Without rounding:
TRUNCATE(0.166, 2)
-- will be evaluated to 0.16
TRUNCATE(0.164, 2)
-- will be evaluated to 0.16
docs: http://www.w3resource.com/mysql/mathematical-functions/mysql-truncate-function.php
With rounding:
ROUND(0.166, 2)
-- will be evaluated to 0.17
ROUND(0.164, 2)
-- will be evaluated to 0.16
docs: http://www.w3resource.com/mysql/mathematical-functions/mysql-round-function.php
Solution 2 - Mysql
You want to use the TRUNCATE command.
https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_truncate
Solution 3 - Mysql
How about
CAST(2229.999 AS DECIMAL(6,2))
to get a decimal with 2 decimal places
Solution 4 - Mysql
Just use
format(number, qtyDecimals)
sample: format(1000, 2)
result 1000.00
Solution 5 - Mysql
This is how I used this is as an example:
CAST(vAvgMaterialUnitCost.`avgUnitCost` AS DECIMAL(11,2)) * woMaterials.`qtyUsed` AS materialCost
Solution 6 - Mysql
Show as decimal Select ifnull(format(100.00, 1, 'en_US'), 0) 100.0
Show as Percentage Select concat(ifnull(format(100.00, 0, 'en_US'), 0), '%') 100%