Familiarize yourself with these usage considerations before using date and time data types.
Date and Time Data Type |
Description |
|---|---|
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. |
SMALLDATETIME |
A domain, implemented as TIMESTAMP. SMALLDATETIME is provided primarily for compatibility with Adaptive Server Enterprise. |
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 datetime, 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.000', '7911-12-30 00:00:44.000'
When you select using cast, you see the complete timestamp:
select id, descript, cast(datetime_null as char(21)), cast(timestamp_null as char(21)) from mydates;
1, 'example', '7911-12-30 23:59:59.0', '7911-12-30 06:03:44.0'