DATE data type based on TIMESTAMP(0)

The DATE data type supports applications that use the Oracle DATE data type and expect that the DATE values include time information (for example, '2009-04-01-09.43.05'). This capability does not apply to the Entry plan of the Db2® Warehouse on Cloud managed service.

Effects

When the DATE data type is explicitly encountered in SQL statements, it is implicitly mapped to TIMESTAMP(0) in most cases. An exception is the specification of SQL DATE in the xml-index-specification clause of a CREATE INDEX statement. As a result of the implicit mapping, messages refer to the TIMESTAMP data type instead of DATE, and any operations that describe data types for columns or routines return TIMESTAMP instead of DATE.

Datetime literal support is changed as follows:
  • The value of an explicit DATE literal is a TIMESTAMP(0) value in which the time portion is all zeros. For example, DATE '2008-04-28' represents the timestamp value '2008-04-28-00.00.00'.
  • The database manager supports two additional formats for the string representation of a date, which correspond to 'DD-MON-YYYY' and 'DD-MON-RR'. Only English abbreviations of the month are supported. For example, '28-APR-2008' or '28-APR-08' can be used as string representations of a date, which represents the TIMESTAMP(0) value '2008-04-28-00.00.00'.
    Starting from Version 9.7 Fix Pack 6, the database manager also supports the following formats for the string representation of a date in English only:
    • 'DDMONYYYY' or 'DDMONRR'
    • 'DD-MONYYYY' or 'DD-MONRR'
    • 'DDMON-YYYY' or 'DDMON-RR'
    For example, the following strings all represent the TIMESTAMP(0) value '2008-04-28-00.00.00':
    • '28APR2008' or '28APR08'
    • '28-APR2008' or '28-APR08'
    • '28APR-2008' or '28APR-08'
    For a description of the format elements, see TIMESTAMP_FORMAT scalar function .

The CURRENT_DATE (also known as CURRENT DATE) special register returns a TIMESTAMP(0) value that is the same as the CURRENT_TIMESTAMP(0) value.

When you add a numeric value to a TIMESTAMP value or subtract a numeric value from a TIMESTAMP value, it is assumed that the numeric value represents a number of days. The numeric value can have any numeric data type, and any fractional value is considered to be a fractional portion of a day. For example, TIMESTAMP '2008-03-28 12:00:00' + 1.3 adds 1 day, 7 hours, and 12 minutes to the TIMESTAMP value, resulting in '2008-03-29 19:12:00'. If you are using expressions for partial days, such as 1/24 (1 hour) or 1/24/60 (1 minute), ensure that the number_compat database configuration parameter is set to ON so that the division is performed using DECFLOAT arithmetic.

The results of some functions change:
  • If you pass a string argument to the ADD_YEARS, ADD_MONTHS, or ADD_DAYS scalar function, it returns a TIMESTAMP(0) value.
  • The DATE scalar function returns a TIMESTAMP(0) value for all input types.
  • If you pass a string argument to the FIRST_DAY or LAST_DAY scalar function, it returns a TIMESTAMP(0) value.
  • If you pass a DATE argument to the ADD_YEARS, ADD_MONTHS, ADD_DAYS, ADD_HOURS, ADD_MINUTES, ADD_SECONDS, LAST_DAY, NEXT_DAY, ROUND, or TRUNCATE scalar function, the function returns a TIMESTAMP(0) value.
  • The adding of one date value to another returns TIMESTAMP(0) value.
  • Subtracting one timestamp value from another returns DECFLOAT(34), which represents the difference as a number of days, with exception to expressions that are used to define the following scalar functions:
    • YEARS_BETWEEN
    • YMD_BETWEEN
    • AGE
    The expressions in these scalar functions retain the existing semantic of returning a timestamp duration.
  • Subtracting one date value from another returns DECFLOAT(34), which represents a number of days.
  • If you specify the fractional second format (FF) for the TO_DATE or TO_TIMESTAMP function, the fractional second format is equivalent to specifying 10-9 precision value (FF9).
  • The second parameter in the TIMESTAMPDIFF scalar function does not represent a timestamp duration. Rather it represents the difference between two timestamps as a number of days. The returned estimate may vary by a number of days. For example, if the number of months (interval 64) is requested for the difference between '2010-03-31-00.00.00.000000' and '2010-03-01-00.00.00.000000', the result is 1. This is because the difference between the timestamps is 30 days, and the assumption of 30 days in a month applies. The following table shows how the returned value is determined for each interval.
    Table 1. TIMESTAMPDIFF computations
    Result interval Computation using the difference between two timestamps as a number of days
    Years integer value of (days/365)
    Quarters integer value of (days/90)
    Months integer value of (days/30)
    Weeks integer value of (days/7)
    Days integer value of days
    Hours integer value of (days*24)
    Minutes (the absolute value of the number of days must not exceed 1491308.0888888888888882) integer value of (days*24*60)
    Seconds (the absolute value of the number of days must be less than 24855.1348148148148148) integer value of (days*24*60*60)
    Microseconds (the absolute value of the number of days must be less than 0.02485513481481481) integer value of (days*24*60*60*1000000)

If you use the import or load utility to input data into a DATE column, you must use the timestampformat file type modifier instead of the dateformat file type modifier.