The TRUNC_TIMESTAMP scalar
function returns a TIMESTAMP that is an argument (expression)
truncated to the unit specified by another argument (format-string).
.-,--'DD'---------------------------.
>>-TRUNC_TIMESTAMP--(--expression--+-----------------------------------+--)-><
'-,--format-string--+-------------+-'
'-locale-name-'
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 254 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
The result can be null; if any argument is null, the result is
the null value.
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
Invocations
of the function that depend on the value of a special register cannot
be used wherever special registers cannot be used (SQLSTATE 42621,
428EC, or 429BX).
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.