DB2 Scalar functions - DATE_TRUNC


Volta a página anterior

Volta ao Menu das scalar functions

Volta ao Menu Principal


Desenvolvido por DORNELLES Carlos Alberto - Analista de Sistemas - Brasília DF. - cad_cobol@hotmail.com

DATE_TRUNC

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

DATE_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, 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.

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
  • A DECIMAL value representing a date, time, or timestamp duration, the result is a DECIMAL value of the same type

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

Examples

  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
  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
  3. Truncate a TIME value to the beginning of the minute. 
    values date_trunc('MINUTE', TIME('20:38:40'));
Result: 20:38:00
  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. 
    values date_trunc('MONTH', DECIMAL('00200203'));
Result: 00200200
  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. 
    values date_trunc('MINUTE', DECIMAL('102930'));
Result: 102900
  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. 
    values date_trunc('HOUR', DECIMAL('00070005032040.000301'));
Result: 70005030000.000000
© Copyright IBM Corp.