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).

Read syntax diagramSkip visual syntax diagramTRUNC_TIMESTAMP(expression ,'DD',format-stringlocale-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 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
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.