.-,--0-------------------. >>-+-TRUNCATE-+--(--numeric-expression1--+------------------------+--)->< '-TRUNC----' '-,--numeric-expression2-'

.-,--'DD'---------------------------. >>-+-TRUNCATE-+--(--datetime-expression--+-----------------------------------+--)->< '-TRUNC----' '-,--format-string--+-------------+-' '-locale-name-'

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

The
TRUNCATE function returns a truncated value of:

- A number, truncated to the specified number of places to the right or left of the decimal point, if the result of the first argument is a numeric value
- A datetime value, truncated to the unit specified by format-string, if the first argument is a DATE, TIME, or TIMESTAMP

- 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 on 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 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.

**Determinism:**TRUNCATE is a deterministic function. However, the following invocations of the function depend on the value of the special register CURRENT LOCALE LC_TIME.- Truncate of a datetime value when locale-name is
not explicitly specified and one of the following is true:
- format-string is not a constant
- format-string is a constant and includes format elements that are locale sensitive

- Truncate of a datetime value when locale-name is
not explicitly specified and one of the following is true:

- 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.

Assuming the highest paid employee earns $52750.00 per year, the example returns 4395.83.**SELECT TRUNCATE**(**MAX**(SALARY)/12,2)**FROM**EMPLOYEE; - Display the number 873.726 truncated 2, 1, 0, -1, and -2 decimal
places, respectively.

This example returns 873.720, 873.700, 873.000, 870.000, 800.000, and 0.000.**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) ); - Display the decimal-floating point number 873.726
truncated 0 decimal places.

Returns the value 873.**VALUES**(**TRUNCATE**(**DECFLOAT**(873.726),0)) - Set the variable vTRNK_DT with the input date
rounded to the nearest month value.

The value set is 2000-08-01.`SET vTRNK_DT = TRUNC(DATE('2000-08-16'), 'MONTH');`

- Set the host variable TRNK_TMSTMP with the current
year rounded to the nearest year value.

The value set is 2000-01-01-00.00.00.000000.`SET :TRNK_TMSTMP = TRUNCATE('2000-03-14-17.30.00'), 'YEAR');`