Categories:

Numeric functions (Rounding and Truncation)

TRUNCATE , TRUNC¶

Rounds the input expression down to the nearest (or equal) integer closer to zero, or to the nearest equal or smaller value with the specified number of places after the decimal point.

These functions are synonymous.

Note

TRUNC is overloaded; it can also be used as a date/time function to truncate dates, times, and timestamps to a specified part.

See also:

CEIL , FLOOR , ROUND

Syntax¶

TRUNCATE( <input_expr> [ , <scale_expr> ] )

TRUNC( <input_expr> [ , <scale_expr> ] )
Copy

Arguments¶

input_expr

The value or expression to operate on. The data type should be one of the numeric data types, such as FLOAT or NUMBER.

scale_expr

The number of digits the output should include after the decimal point. The expression should evaluate to an integer from -38 to +38.

The default scale_expr is zero, meaning that the function removes all digits after the decimal point.

For information about negative scales, see the Usage Notes below.

Returns¶

The data type of the returned value is NUMBER(precision, scale).

If the input scale was greater than or equal to zero, then the output scale generally matches the input scale.

If the input scale was negative, then the output scale is 0.

For example:

  • The data type returned by TRUNCATE(3.14, 1) is NUMBER(4, 1).

  • The data type returned by TRUNCATE(3.14, 0) is NUMBER(4, 0).

  • The data type returned by TRUNCATE(33.33, -1) is NUMBER(5, 0).

If the scale is zero, then the value is effectively an integer.

Usage notes¶

  • If scale_expr is negative, then it specifies the number of places before the decimal point to which to adjust the number. For example, if the scale is -2, then the result is a multiple of 100.

  • If scale_expr is larger than the input expression scale, the function does not have any effect.

  • If either the input_expr or the scale_expr is NULL, then the result is NULL.

  • Truncation is performed towards 0, not towards the smaller number. For example, TRUNCATE(-9.6) results in -9, not -10.

Examples¶

The following examples demonstrate the TRUNC function.

SELECT DISTINCT n, TRUNCATE(n) 
  FROM test_1
  ORDER BY n;
+----------+-------------+
|        N | TRUNCATE(N) |
|----------+-------------|
| -975.975 |        -975 |
|  135.135 |         135 |
+----------+-------------+
Copy
SELECT n, scale, TRUNC(n, scale) 
  FROM test_1 
  ORDER BY n, scale;
+----------+-------+-----------------+
|        N | SCALE | TRUNC(N, SCALE) |
|----------+-------+-----------------|
| -975.975 |    -1 |        -970     |
| -975.975 |     0 |        -975     |
| -975.975 |     2 |        -975.97  |
|  135.135 |    -2 |         100     |
|  135.135 |     0 |         135     |
|  135.135 |     1 |         135.1   |
|  135.135 |     3 |         135.135 |
|  135.135 |    50 |         135.135 |
|  135.135 |  NULL |            NULL |
+----------+-------+-----------------+
Copy
Language: English