The TRUNC_TIMESTAMP 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.

Read syntax diagramSkip visual syntax diagramTRUNC_TIMESTAMP(expression ,'DD',format-string )
An expression that returns a value of one of the following built-in data types: a date, a timestamp, a character-string, or a graphic-string.
If expression is a character or graphic string, its value must be a valid string representation of a date or timestamp. It is first converted to a TIMESTAMP(12) value. For the valid formats of string representations of dates and timestamps, see String representations of datetime values.
An expression that returns a built-in character string data type or graphic string data type. format-string contains a template of 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 template for a timestamp. The resulting value is then folded to uppercase, so the characters in the value may be in any case. Allowable values for format-string are listed in Table 1.
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.

If either argument can be null, the result can be null; if either argument is null, the result is the null value.


  • Set the host variable TRN_TMSTMP with the current year truncated to the nearest year value.
      SET :TRN_TMSTMP = TRUNC_TIMESTAMP('2000-03-14-17.30.00', 'YEAR');
    Host variable TRN_TMSTMP is set with the value 2000-01-01-