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').

Enablement

You enable DATE as TIMESTAMP(0) support at the database level, before creating the database where you require the support.
To enable the support, set the DB2_COMPATIBILITY_VECTOR< registry variable to hexadecimal value 0x40 (bit position 7), and then stop and restart the instance to have the new setting take effect.

   db2set DB2_COMPATIBILITY_VECTOR=40
   db2stop
   db2start

To take full advantage of the Db2® compatibility features for Oracle applications, the recommended setting for the DB2_COMPATIBILITY_VECTOR is ORA, which sets all of the compatibility bits.

After you create a database with DATE as TIMESTAMP(0) support enabled, the date_compat database configuration parameter is set to ON.

If you create a database with DATE as TIMESTAMP(0) support enabled, you cannot disable that support for that database, even if you reset the DB2_COMPATIBILITY_VECTOR registry variable.
Similarly, if you create a database with DATE as TIMESTAMP(0) support disabled, you cannot enable that support for that database later, even by setting the DB2_COMPATIBILITY_VECTOR registry variable.

Effects

The date_compat database configuration parameter indicates whether the DATE compatibility semantics associated with the TIMESTAMP(0) data type are applied to the connected database.
The effects of setting date_compat to ON are as follows.

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.