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:
  T RUNC_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');