Mysql decimal round

Question

I am new to mysql and need some basic things.

I need to round decimal numbers 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 many things, but could not do it.

Thanks.

Answer 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 

Answer 2

Try the following:

 // 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 is not a complete solution, but perhaps it will point you in the right direction.

Read the documentation for mysql round() and mysql truncate()