TRUNCATE or TRUNC

The TRUNCATE function returns the first argument, truncated as specified. Truncation is to the number of places to the right or left of the decimal point this is specified by the second argument.


                                          .-,--0--------------------.      
>>-+-TRUNCATE-+--(--numeric-expression-1--+-------------------------+--)-><
   '-TRUNC----'                           '-,--numeric-expression-2-'

The schema is SYSIBM.

numeric-expression-1

An expression that returns a value of any built-in numeric data type.

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 you want a different rounding behavior, use the QUANTIZE function.

Start of change The argument can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34). End of change

numeric-expression-2

An expression that returns a value that is a built-in SMALLINT or INTEGER data type. The absolute value of the integer specifies the number of places to truncate. The value of numeric-expression-2 determines whether truncation is to the right or left of the decimal point.

If numeric-expression-2 is not negative, numeric-expression-1 is truncated to the absolute value of numeric-expression-2 places to the right of the decimal point.

If numeric-expression-2 is negative, numeric-expression-1 is truncated to 1 + (the absolute value of numeric-expression-2 ) places to the left of the decimal point. If 1 + (the absolute value of numeric-expression-2) is greater than or equal to the number of digits to the left of the decimal point, the result is 0. For example, TRUNCATE(748.58,-4) returns 0.

Start of change The argument can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34), which is then assigned to an INTEGER value. End of change

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

The result can be null; if any argument is null, the result is the null value.

Example 1: Using sample employee table DSN8A10.EMP, 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 DSN8A10.EMP;

Because the highest paid employee in the sample employee table earns $52750.00 per year, the example returns the value 4395.83.

Example 2: Return the number 873.726 truncated to 2, 1, 0, -1, -2, -3, and -4 decimal places respectively.

   SELECT TRUNC(873.726,2),
          TRUNC(873.726,1),
          TRUNC(873.726,0),
          TRUNC(873.726,-1),
          TRUNC(873.726,-2),
          TRUNC(873.726,-3),
          TRUNC(873.726,-4)
     FROM TABLEX
     WHERE INTCOL = 1234;

This example returns the values 873.720, 873.700, 873.000, 870.000, 800.000, 0000.000, and 0000.000.

Example 3: Calculate both positive and negative numbers.

SELECT TRUNCATE( 3.5, 0),
	TRUNCATE( 3.1, 0),
	TRUNCATE(-3.1, 0),
	TRUNCATE(-3.5, 0)
	FROM TABLEX;

This example returns: the values 3.0, 3.0, -3.0, -3.0.