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.
- 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.
- 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.
Host variable TRN_TMSTMP is set with the value 2000-01-01-00.00.00.000000.
SET :TRN_TMSTMP = TRUNC_TIMESTAMP('2000-03-14-17.30.00', 'YEAR');