TRUNC_TIMESTAMP scalar function
The TRUNC_TIMESTAMP scalar function returns a TIMESTAMP that is an argument (expression) truncated to the unit specified by another argument (format-string).
The schema is SYSIBM.
If format-string is not specified, expression is truncated to the nearest day, as if 'DD' was specified for format-string.
- expression
- An expression that returns a value of one of the following built-in data types: a DATE or a 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
expression should be truncated. For example, if
format-string is 'DD', a timestamp that is represented by
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 a
timestamp (SQLSTATE 22007). The default is 'DD'.
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 model 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 is a TIMESTAMP with the same timestamp precision as expression. The result can be null; if any argument is null, the result is the null value.
The result of the function
is a TIMESTAMP with a timestamp precision of:
- p when the data type of expression is TIMESTAMP(p)
- 0 when the data type of expression is DATE
- 6 otherwise
Notes
- Determinism: TRUNC_TIMESTAMP 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 date or timestamp 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 date or timestamp value when locale-name is
not explicitly specified and one of the following is true:
Example
Set
the host variable TRNK_TMSTMP with the current
year rounded to the nearest year value.
SET :TRNK_TMSTMP = TRUNC_TIMESTAMP('2000-03-14-17.30.00', 'YEAR');
The
host variable TRNK_TMSTMP is set with the value 2000-01-01-00.00.00.000000.