For storing dates and times.
DATE
DATETIME
SMALLDATETIME
TIME
TIMESTAMP
DATE A calendar date, such as a year, month and day. The year can be from the year 0001 to 9999. The day must be a non-zero 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 you should 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 non-zero 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 may 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.
This example first creates a table with DATETIME and TIMESTAMP columns, and 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, the 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'
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.
