Syntax error What is the significant difference between MySQL TRUNCATE() and ROUND() function?

What is the significant difference between MySQL TRUNCATE() and ROUND() function?

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)
Updated on: 2020-06-20T13:11:16+05:30

5K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements