DATE_TRUNC scalar function

THE DATE_TRUNC function truncates a date, time, or timestamp value to the specified time unit.

Read syntax diagramSkip visual syntax diagramDATE_TRUNC(format-string ,datetime-expression)

The schema is SYSIBM.

format-string

An expression that returns a character string that does not exceed 255 bytes and that, after all leading and trailing blanks are removed, is one of the character strings listed in Table 1. The resulting substring must be a valid format element for the type of the specified datetime expression (SQLSTATE 22007). For example, a DATE value cannot be truncated to its first hour, minute, or second, and a TIME value cannot be truncated to the first day of its year.

datetime-expression
An expression that returns a DATE, TIME, or TIMESTAMP value. String representations of these data types must be explicitly cast to a DATE, TIME, or TIMESTAMP value.
Table 1. Allowed values for a format string
If the format string is... the datetime expression is truncated to the... For example... is truncated to...
'MILLENNIUM' or 'MILLENNIUMS' First day of its millennium. 1999-02-14 1000-01-01
'CENTURY' or 'CENTURIES' First day of its century. 1999-02-14 1900-01-01
'DECADE' or 'DECADES' First day of its decade. 1999-02-14 1990-01-01
'YEAR' or 'YEARS' First day of its year. 1999-02-14 1999-01-01
'QUARTER' First day of its quarter. 2017-05-14 20:38:40.24 2017-04-01 00:00:00
'MONTH' or 'MONTHS' First day of its month 2017-05-14 20:38:40.24 2017-05-01 00:00:00
'WEEK' First day of its week. 2017-05-14 20:38:40.24 2017-05-08 00:00:00
'DAY' or 'DAYS' Beginning of its day. 2017-05-14 20:38:40.24 2017-05-14 00:00:00
'HOUR' or 'HOURS' Beginning of its hour. 2017-05-14 20:38:40.24 2017-05-14 20:00:00
'MINUTE' or 'MINUTES' Beginning of its minute. 20:38:40.24576985 20:38:00
'SECOND' or 'SECONDS' Beginning of its second. 20:38:40.24576985 20:38:40
'MILLISECOND' or 'MILLISECONDS' Beginning of its millisecond. 20:38:40.24576985 20:38:40.245
'MICROSECOND' or 'MICROSECOND' Beginning of its microsecond. 20:38:40.24576985 20:38:40.245769
The format-string values are case insensitive.

Result

If the specified datetime expression is:
  • A DATE or TIMESTAMP value, the result is a TIMESTAMP value
  • A TIME value, the result is a TIME value
If the argument can be null, the result can be null; if the argument is null, the result is the null value.

Examples

  • Example 1: Truncate a DATE value to the beginning of the month.
    values date_trunc('MONTH', DATE('2007-02-18'))
      Result: 2007-02-01 00:00:00
  • Example 2: Truncate a TIMESTAMP value to the beginning of the hour.
    values date_trunc('HOUR', TIMESTAMP('2017-02-14 20:38:40.24'));
     Result: 2017-02-14 20:00:00
  • Example 3: Truncate a TIME value to the beginning of the minute.
     values date_trunc('MINUTE', TIME('20:38:40'));
     Result: 20:38:00