Menu
Rounding a decimal number to two decimal places is the same as rounding it to the hundredths place, which is the second place to the right of the decimal point. For example, 2.83620364 can be round to two decimal places as 2.84, and 0.7035 can be round to two decimal places as 0.70.
On the Formulas tab, under Function, click Formula Builder. In number, type the number you are rounding up. In num_digits, type 0 to round the number up to the nearest whole number. In number, type the number you are rounding down.
Click the Table Tools' Layout tab, select Data and then click Formula. Click the Number Format menu and select 0.00 for two decimals.
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
You want to use the TRUNCATE command.
https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_truncate
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
This is how I used this is as an example:
CAST(vAvgMaterialUnitCost.`avgUnitCost` AS DECIMAL(11,2)) * woMaterials.`qtyUsed` AS materialCost
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%
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With