Date and time data types

Description

For storing dates and times.

Syntax

DATE

DATETIME

SMALLDATETIME

TIME

TIMESTAMP

Usage

DATE A calendar date, such as a year, month and day. The year can be from 0001 to 9999. The day must be a nonzero value, so that the minimum date is 0001-01-01. A DATE value requires 4 bytes of storage.

DATETIME A domain, implemented as TIMESTAMP. DATETIME is provided primarily for compatibility with Adaptive Server Enterprise. For an exception, see “Compatibility of string to datetime conversions”.

SMALLDATETIME A domain, implemented as TIMESTAMP. SMALLDATETIME is provided primarily for compatibility with Adaptive Server Enterprise. For an exception, see “Compatibility of string to datetime conversions”.

TIME Time of day, containing hour, minute, second, and fraction of a second. The fraction is stored to 6 decimal places. A TIME value requires 8 bytes of storage. (ODBC standards restrict TIME data type to an accuracy of seconds. For this reason, do not use TIME data types in WHERE clause comparisons that rely on a higher accuracy than seconds.)

TIMESTAMP Point in time, containing year, month, day, hour, minute, second, and fraction of a second. The fraction is stored to 6 decimal places. The day must be a nonzero value. A TIMESTAMP value requires 8 bytes of storage.

The valid range of the TIMESTAMP data type is from 0001-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999. The display of TIMESTAMP data outside the range of 1600-02-28 23:59:59 to 7911-01-01 00:00:00 might be incomplete, but the complete datetime value is stored in the database; you can see the complete value by first converting it to a character string. You can use the CAST() function to do this, as in the following example, which first creates a table with DATETIME and TIMESTAMP columns, then inserts values where the date is greater 7911-01-01.

create table mydates (id int, descript char(20),
  datetime_null datatime, timestamp_null timestamp);

insert into mydates values (1, 'example', '7911-12-30
  23:59:59','7911-12-30 06:03:44');
commit;

When you select without using CAST, hours and minutes are set to 00:00:

select * from mydates;

1, 'example', '7911-12-30 00:00:59', '7911-12-30 00:00:44'

When you select using cast, you see the complete timestamp:

select id, descript, cast(datatime_null as char(21)),
  cast(timestamp_null as char(21)) from mydates;

1, 'example', '7911-12-30 23:59:59', '7911-12-30 06:03:44'

Notes

The following index types are supported by date and time data:

All date and time data types support the CMP, HG, HNG, and LF index types; the WD index type is not supported.
DATE data supports the DATE index.
TIME data supports the TIME index.
DATETIME and TIMESTAMP data support the DTTM index.