TRUNCATE or TRUNC scalar function

TRUNCATE numeric:

TRUNCATE datetime:

The schema is SYSIBM. The SYSFUN version of the TRUNCATE numeric function continues to be available.

TRUNCATE numeric

If numeric-expression1 has a numeric data type, the TRUNCATE function returns numeric-expression1 truncated to numeric-expression2 places to the right of the decimal point if numeric-expression2 is positive, or to the left of the decimal point if numeric-expression2 is zero or negative. If numeric-expression2 is not specified, numeric-expression1 is truncated to zero places left of the decimal point.

numeric-expression1

An expression that must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, or numeric data type. If the value is not a numeric data type, it is implicitly cast to DECFLOAT(34) before evaluating the function.

If the expression 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.

numeric-expression2

An expression that returns a value that is a built-in numeric data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function.

If numeric-expression2 is not negative, numeric-expression1 is truncated to the absolute value of numeric-expression2 number of places to the right of the decimal point.

If numeric-expression2 is negative, numeric-expression1 is truncated to the absolute value of numeric-expression2 + 1 number of places to the left of the decimal point. If the absolute value of a negative numeric-expression2 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, length, and scale attributes of the result are the same as the data type, length, and scale attributes 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.

TRUNCATE datetime

If datetime-expression has a datetime data type, the TRUNCATE function returns datetime-expression rounded to the unit specified by the format-string. If format-string is not specified, datetime-expression is truncated to the nearest day, as if 'DD' is specified for format-string.

datetime-expression

An expression that must return a value that is a DATE, a TIME, or a TIMESTAMP. String representations of these data types are not supported and must be explicitly cast to a DATE, TIME, or TIMESTAMP for use with this function; alternatively, you can use the TRUNC_TIMESTAMP function for a string representation of a date or timestamp.

format-string

An expression that returns a built-in character string data type with an actual length that is not greater than 254 bytes. The format element in format-string specifies how datetime-expression should be truncated. For example, if format-string is 'DD', a timestamp that is represented by datetime-expression is truncated to the nearest day. Leading and trailing blanks are removed from the string, and the resulting substring must be a valid format element for the type of datetime-expression (SQLSTATE 22007). The default is 'DD', which cannot be used if the data type of datetime-expression is TIME.

Allowable values for format-string are listed in the table of format elements found in the description of the ROUND function.

locale-name

A character constant that specifies the locale used to determine the first day of the week when using format element values DAY, DY, or D. The value of locale-name is not case sensitive and must be a valid locale (SQLSTATE 42815). For information about valid locales and their naming, see "Locale names for SQL and XQuery". If locale-name is not specified, the value of the special register CURRENT LOCALE LC_TIME is used.

The result of the function has the same date type as datetime-expression. The result can be null; if any argument is null, the result is the null value.

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

The result can be null if the argument can be null or if the argument is not a decimal floating-point number and the database is configured with dft_sqlmathwarn set to YES; the result is the null value if the argument is null.

Notes

Examples

• Example 1: Using the EMPLOYEE table, calculate the average monthly salary for the highest paid employee. Truncate the result two places to the right of the decimal point.

     SELECT TRUNCATE(MAX(SALARY)/12,2)
       FROM EMPLOYEE;

  Assuming the highest paid employee earns $52750.00 per year, the example returns 4395.83.
• Example 2: Display the number 873.726 truncated 2, 1, 0, -1, and -2 decimal places, respectively.

     VALUES (
       TRUNCATE(873.726,2),
       TRUNCATE(873.726,1),
       TRUNCATE(873.726,0),
       TRUNCATE(873.726,-1),
       TRUNCATE(873.726,-2),
       TRUNCATE(873.726,-3) );

  This example returns 873.720, 873.700, 873.000, 870.000, 800.000, and 0.000.
• Example 3: Display the decimal-floating point number 873.726 truncated 0 decimal places.

     VALUES(TRUNCATE(DECFLOAT(873.726),0))

  Returns the value 873.
• Example 4: Set the variable vTRNK_DT with the input date rounded to the nearest month value.

     SET vTRNK_DT = TRUNC(DATE('2000-08-16'), 'MONTH');    

  The value set is 2000-08-01.
• Example 5: Set the host variable TRNK_TMSTMP with the current year rounded to the nearest year value.

     SET :TRNK_TMSTMP = TRUNCATE('2000-03-14-17.30.00'), 'YEAR');

  The value set is 2000-01-01-00.00.00.000000.