Question

difference between truncate and round in mysql

Answer 1

The TRUNCATE() function is used to return the value of X truncated to D number of decimal places. If D is 0, then the decimal point is removed. If D is negative, then D number of values in the integer part of the value is truncated. Consider the following example –

mysql> Select TRUNCATE(7.536432,2); +----------------------+ | TRUNCATE(7.536432,2) | +----------------------+ |                 7.53 | +----------------------+ 1 row in set (0.00 sec)

The ROUND() function returns X rounded to the nearest integer. If a second argument, D, is supplied, then the function returns X rounded to D decimal places. D must be positive or all digits to the right of the decimal point will be removed. Consider the following example −  

mysql>SELECT ROUND(5.693893); +---------------------------------------------------------+ |                    ROUND(5.693893)                      | +---------------------------------------------------------+ |                           6                             | +---------------------------------------------------------+ 1 row in set (0.00 sec)   mysql>SELECT ROUND(5.693893,2); +---------------------------------------------------------+ |                   ROUND(5.693893,2)                     | +---------------------------------------------------------+ |                          5.69                           | +---------------------------------------------------------+ 1 row in set (0.00 sec)  

From the above definition and examples we can observe the following difference between these two functions:  

ROUND() function rounds the number up or down depends upon the second argument D and the number itself(digit after D decimal places >=5 or not).  

TRUNCATE() function truncate the number up to D number of decimal places without checking whether the digit after D decimal >=5 or not.  

mysql> Select ROUND(1.289,2)AS 'AFTER ROUND',TRUNCATE(1.289,2)AS 'AFTER TRUNCATE'; +-------------+----------------+ | AFTER ROUND | AFTER TRUNCATE | +-------------+----------------+ |        1.29 |           1.28 | +-------------+----------------+ 1 row in set (0.00 sec)