.-,--'DD'---------------------------.
>>-TRUNC_TIMESTAMP--(--expression--+-----------------------------------+--)-><
'-,--format-string--+-------------+-'
'-locale-name-'
The schema is SYSIBM.
The TRUNC_TIMESTAMP scalar
function returns a TIMESTAMP that is the expression truncated
to the unit specified by the format-string.
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
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 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.
Examples
- 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.
DB2 Version 9.7 for Linux, UNIX, and Windows