DATE_TRUNC scalar function
THE DATE_TRUNC function truncates a date, time, or timestamp value to the specified time unit.
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, or a DECIMAL value representing a date, time, or timestamp duration. String representations of these data types must be explicitly cast to a DATE, TIME, TIMESTAMP, or DECIMAL value. For more information about durations, see Datetime operations and durations.
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 |
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
- A DECIMAL value representing a date, time, or timestamp duration, the result is a DECIMAL value of the same type
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
- Example 4: A date duration has the data type DECIMAL(8,0). For
example, the value 00200203 represents a duration of 20 years, 2 months, and 3 days. Truncate a date
duration to the beginning of the
month.
db2 "values date_trunc('MONTH', cast ('00200203' AS DECIMAL(8,0)))" 1 ---------- 200200. 1 record(s) selected.
- Example 5: A time duration has the data type DECIMAL(6,0). For
example, the value 102930 represents a duration of 10 hours, 29 minutes, and 30 seconds. Truncate a
time duration to the beginning of the
minute.
db2 "values date_trunc('MINUTE', cast ('102930' AS DECIMAL(6,0)))" 1 -------- 102900. 1 record(s) selected.
- Example 6: A timestamp duration has the data type
DECIMAL(14+s,s), where s is the timestamp precision. For example, the
DECIMAL(20,6) value 00070005032040.000301 represents a duration of 7 years, 0 months, 5 days, 3
hours, 20 minutes, and 40.000301 seconds. Truncate a timestamp duration to the beginning of the
hour.
db2 "values date_trunc('HOUR', cast ('00070005032040.000301' AS DECIMAL(15,1)))" 1 ----------------- 70005030000.0 1 record(s) selected.