ROUND_TIMESTAMP scalar function

The ROUND_TIMESTAMP scalar function returns a TIMESTAMP based on a provided argument (expression), rounded to the unit specified in another argument (format-string).

Read syntax diagramSkip visual syntax diagramROUND_TIMESTAMP(expression ,'DD',format-stringlocale-name )

The schema is SYSIBM.

If format-string is not specified, expression is rounded to the nearest day, as if 'DD' is 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 rounded. For example, if format-string is 'DD', the timestamp that is represented by expression is rounded 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 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: ROUND_TIMESTAMP is a deterministic function. However, the following invocations of the function depend on the value of the special register CURRENT LOCALE LC_TIME.
    • Round 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 RND_TMSTMP with the input timestamp rounded to the nearest year value. 

     SET :RND_TMSTMP = ROUND_TIMESTAMP('2000-08-14-17.30.00', 'YEAR');
The value set is 2001-01-01-00.00.00.000000.