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))
This question is related to
mysql
formatting
decimal
You want to use the TRUNCATE command.
https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_truncate
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%
This is how I used this is as an example:
CAST(vAvgMaterialUnitCost.`avgUnitCost` AS DECIMAL(11,2)) * woMaterials.`qtyUsed` AS materialCost
How about
CAST(2229.999 AS DECIMAL(6,2))
to get a decimal with 2 decimal places
Just use format(number, qtyDecimals) sample: format(1000, 2) result 1000.00
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
Source: Stackoverflow.com