MariaDB ROUND() vs TRUNCATE()

Posted on by Ian

MariaDB has a ROUND() function and a TRUNCATE() function that can return the same results or different results, depending on the exact value of their arguments.

As the function names suggest, ROUND() rounds the number and TRUNCATE() truncates the number. Truncating a number simply cuts it off without performing any rounding.

Below is a quick rundown on the difference between ROUND() and TRUNCATE() in MariaDB.

The Difference

Here’s the difference between each function in a nutshell:

  • ROUND() rounds its argument to a specified number of decimal places. This will sometimes cause the result to be rounded up, and sometimes not.
  • TRUNCATE() simply truncates its argument to a specified number of decimal places. No rounding occurs.

Syntax & Definitions

First, here are the syntaxes and definitions of each function.

ROUND()

The ROUND() function can be used in the following two ways:

ROUND(X)
ROUND(X,D)

ROUND() rounds the argument X to D decimal places. The rounding algorithm depends on the data type of X.

TRUNCATE()

The TRUNCATE() function can be used with only one syntax:

TRUNCATE(X,D)

TRUNCATE() returns the number X, truncated to D decimal places.

Example

Here’s a comparison to demonstrate the difference between ROUND() and TRUNCATE():

SELECT 
    TRUNCATE(3.6789, 2),
    ROUND(3.6789, 2);

Result:

+---------------------+------------------+
| TRUNCATE(3.6789, 2) | ROUND(3.6789, 2) |
+---------------------+------------------+
|                3.67 |             3.68 |
+---------------------+------------------+

In this case, the ROUND() function rounded the number up, because the next digit (8) is greater than 5.

The TRUNCATE() function on the other hand, simply truncated the number at the specified decimal place. TRUNCATE() doesn’t do any rounding. It simply cuts off the number at the specified place.

Both functions accept a negative value for the decimal place, and the same respective logic applies:

SELECT 
    TRUNCATE(36789, -2),
    ROUND(36789, -2);

Result:

+---------------------+------------------+
| TRUNCATE(36789, -2) | ROUND(36789, -2) |
+---------------------+------------------+
|               36700 |            36800 |
+---------------------+------------------+

Same Result

Both functions can sometimes return the same result. It all depends on the value of the arguments being passed.

Here’s an example where they both return the same result:

SELECT 
    TRUNCATE(3.6749, 2),
    ROUND(3.6749, 2);

Result:

+---------------------+------------------+
| TRUNCATE(3.6749, 2) | ROUND(3.6749, 2) |
+---------------------+------------------+
|                3.67 |             3.67 |
+---------------------+------------------+

In this case, ROUND() didn’t round the number up, because the next digit (4) was less than 5. Therefore, both results are the same.

The FLOOR() Function

Both functions are different to the FLOOR() function, which returns the largest integer value not greater than its argument. FLOOR() doesn’t accept a second argument like ROUND() and TRUNCATE() do (it only ever returns an integer anyway).