TRUNCATE or TRUNC

The TRUNCATE function returns expression–1 truncated to some number of places to the right or left of the decimal point.

TRUNCATETRUNC(expression-1Start of change,0,expression-2End of change)
expression–1
An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. A string argument is converted to double-precision floating point before evaluating the function. For more information about converting strings to double-precision floating point, see DOUBLE_PRECISION or DOUBLE.

If expression-1 is a decimal floating-point data type, the DECFLOAT ROUNDING MODE will not be used. The rounding behavior of TRUNCATE corresponds to a value of ROUND_DOWN. If a different rounding behavior is wanted, use the QUANTIZE function.

expression–2
An expression that returns a value of a built-in small integer, large integer, or big integer data type. The absolute value of integer specifies the number of places to the right of the decimal point for the result if expression–2 is not negative, or to the left of the decimal point if expression–2 is negative.

If expression–2 is not negative, expression–1 is truncated to the expression–2 number of places to the right of the decimal point.

If expression–2 is negative, expression–1 is truncated to the absolute value of expression–2+1 number of places to the left of the decimal point.

Start of changeIf expression–2 is not specified, expression–1 is truncated to zero places to the left of the decimal point.End of change

If the absolute value of expression–2 is larger than the number of digits to the left of the decimal point, the result is 0. For example, TRUNCATE(748.58,-4) = 0.

The data type and length attribute of the result are the same as the data type and length attribute of the first argument.

If either argument can be null, the result can be null. If either argument is null, the result is the null value.

Examples

  • Calculate the average monthly salary for the highest paid employee. Truncate the result to two places to the right of the decimal point.
      SELECT TRUNCATE(MAX(SALARY/12) , 2)
        FROM EMPLOYEE
    Because the highest paid employee in the sample employee table earns $52750.00 per year, the example returns the value 4395.83.
  • Calculate the number 873.726 truncated to 2, 1, 0, -1, -2, and -3 decimal places respectively.
      SELECT TRUNCATE(873.726,  2),
             TRUNCATE(873.726,  1),
             TRUNCATE(873.726,  0),
             TRUNCATE(873.726, -1),
             TRUNCATE(873.726, -2),
             TRUNCATE(873.726, -3)
        FROM SYSIBM.SYSDUMMY1

    Returns the following values respectively:

    0873.720   0873.700  0873.000  0870.000  0800.000  0000.000
  • Calculate both positive and negative numbers.
      SELECT TRUNCATE( 3.5, 0),
             TRUNCATE( 3.1, 0),
             TRUNCATE(-3.1, 0),
             TRUNCATE(-3.5, 0)
        FROM SYSIBM.SYSDUMMY1
    This example returns:
    3.0   3.0  -3.0  -3.0
    respectively.