DB2 Version 10.1 for Linux, UNIX, and Windows

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

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_MONTHS 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 LAST_DAY scalar function, it returns a TIMESTAMP(0) value.
If you pass a DATE() argument to the ADD_MONTHS, 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.
The subtracting of one timestamp value from another returns DECFLOAT(34), representing the difference as a number of days. Similarly, subtracting one date value from another returns DECFLOAT(34), that represents a number of days.

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 (days2460)
Seconds (the absolute value of the number of days must be less than 24855.1348148148148148)	integer value of (days2460*60)
Microseconds (the absolute value of the number of days must be less than 0.02485513481481481)	integer value of (days2460601000000)

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.