TRUNCATE or TRUNC scalar function
The TRUNCATE function returns a truncated value of a number or a datetime value.
TRUNCATE numeric:
TRUNCATE datetime:
The schema is SYSIBM. The SYSFUN version of the TRUNCATE numeric function continues to be available.
- If the result of the first argument is a numeric value, a number is returned, truncated to the specified number of places to the right or left of the decimal point.
- If the first argument is a DATE, TIME, or TIMESTAMP, a datetime value, truncated to the unit specified by format-string.
- 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 255 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 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
- 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:
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.
Assuming the highest paid employee earns $52750.00 per year, the example returns 4395.83.SELECT TRUNCATE(MAX(SALARY)/12,2) FROM EMPLOYEE;
- Example 2: 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) );
- Example 3: Display the decimal-floating point number 873.726
truncated 0 decimal places.
Returns the value 873.VALUES(TRUNCATE(DECFLOAT(873.726),0))
- Example 4: 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');
- Example 5: 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(DATE('2000-03-14-17.30.00'), 'YEAR');