DB2 Version 9.7 for Linux, UNIX, and Windows

DATE data type based on TIMESTAMP(0)

The DATE data type is changed to support applications that use the Oracle DATE data type expecting that the values include time information (for example, '2009-04-01-09.43.05').

Support for DATE to mean TIMESTAMP(0) is at the database level, and must be enabled before creating the database where support is required. This is achieved by setting the DB2_COMPATIBILITY_VECTOR registry variable to the appropriate value. When a database is created with DATE as TIMESTAMP(0) enabled, the database configuration parameter date_compat is set to ON. After a database is created with DATE as TIMESTAMP(0) support enabled, it cannot be disabled for that database, even if the DB2_COMPATIBILITY_VECTOR registry variable is reset. Similarly, all databases created with DATE as TIMESTAMP(0) support disabled cannot have this support enabled, even by setting the DB2_COMPATIBILITY_VECTOR registry variable.

Enablement

DATE as TIMESTAMP(0) support is enabled by setting bit position number 7 (0x40) of the DB2_COMPATIBILITY_VECTOR registry variable before creating a database. A new setting for the registry variable does not take effect until after the instance has been stopped and then restarted.

Usage

The following support is enabled for a DB2® database that has the date_compat database configuration parameter set to ON.

When the DATE data type is explicitly encountered in SQL statements, it is implicitly mapped to TIMESTAMP(0). An exception is the specification of SQL DATE in the xml-index-specification clause of a CREATE INDEX statement. As a result of this 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 unchanged in a DB2 database that has the date_compat database configuration parameter set to ON, except in the following cases:

The value of an explicit DATE literal returns a TIMESTAMP(0) value in which the time portion is all zeros. For example, DATE '2008-04-28' actually 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' in English only. 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 (and CURRENT DATE) special register returns a TIMESTAMP(0) value that is the same as CURRENT_TIMESTAMP(0).

Adding a numeric value to a TIMESTAMP value or subtracting a numeric value from a TIMESTAMP value assumes 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 are changed under date_compat mode:

The ADD_MONTHS scalar function with a string argument returns TIMESTAMP(0).
The DATE scalar function returns TIMESTAMP(0) for all input types.
The LAST_DAY scalar function with a string argument returns TIMESTAMP(0).
Other scalar functions that returned DATE based on a DATE input argument (ADD_MONTHS, LAST_DAY, NEXT_DAY, ROUND, and TRUNCATE) return TIMESTAMP, because DATE is always a TIMESTAMP(0) under date_compat mode.
Addition of date values returns TIMESTAMP(0), because date values are really TIMESTAMP(0).
Subtraction of timestamp values returns DECFLOAT(34), representing the difference as a number of days. As a result, you can not use the TIMESTAMPDIFF scalar function because it depends on the default behavior of timestamp subtraction. Similarly, subtraction of date values returns DECFLOAT(34), which also represents a number of days, because date values are really TIMESTAMP(0).

For databases that have DATE as TIMESTAMP(0) support enabled, if you use the import or load utility to input data into a DATE column (which is based on TIMESTAMP(0)), you must use the TIMESTAMPFORMAT modifier instead of the DATEFORMAT modifier.