mysql decimal round
15,999
Solution 1
DECLARE @old decimal(38, 10)
DECLARE @p decimal(38, 10)
SET @p = 21.4758
SET @p = @p * 100
SET @p = @p - 0.01
SET @p = ROUND(@p, 0)
SET @p = @p / 100.0
SELECT @p
Solution 2
Try this:
// round the value to two decimal places
SELECT ROUND(<YOUR_FIELD>, 2) field FROM <YOUR_TABLE>
// use truncate if you don't wan't to round the actual value
SELECT TRUNCATE(<YOUR_FIELD>, 2) field FROM <YOUR_TABLE>
// you can also use round or truncate depending whether the third decimal is > 5
SELECT IF(SUBSTR(<YOUR_FIELD>, 5, 1) > 5,
ROUND(<YOUR_FIELD>, 2),
TRUNCATE(<YOUR_FIELD>, 2)) field
FROM <YOUR_TABLE>;
The above isn't a complete solution, but perhaps it will point you in the right direction.
Read the documentation for mysql round()
and mysql truncate()
Author by
Can
Updated on June 04, 2022Comments
-
Can over 1 year
I'm new to mysql and need some basic things.
I need to round the decimals like :
21,4758 should be 21,48
0,2250 should be 0,22
23,0850 should be 23,08
22,9950 should be 22,99
I tried lots of thing but couldn't make it.
Thanks.
-
Can almost 11 yearsit makes 0,2250 to 0,23. doesn't work. 0,2250 should be 0,22.
-
Cyclonecode almost 11 years@Can - I updated my answer, you should use
truncate
overround
, though you wanted to round the value as well. -
Can almost 11 yearsyes thanks Krister, it worked the first three. but this time, 22,9950 returns 23..
-
Cyclonecode almost 11 years@Can - I don't understand why not just round the numbers in an ordinary fashion?