Use the datetime and smalldatetime datatypes to store date and time information from January 1, 1753 through December 31, 9999. Use date for dates from January 1, 0001 to December 31, 9999 or time for 12:00:00 a.m. to 11:59:59:999. Dates outside this range must be entered, stored, and manipulated as char or varchar values.
datetime columns hold dates between January 1, 1753 and December 31, 9999. datetime values are accurate to 1/300 second on platforms that support this level of granularity. Storage size is 8 bytes: 4 bytes for the number of days since the base date of January 1, 1900 and 4 bytes for the time of day.
smalldatetime columns hold dates from January 1, 1900 to June 6, 2079, with accuracy to the minute. Its storage size is 4 bytes: 2 bytes for the number of days after January 1, 1900, and 2 bytes for the number of minutes after midnight.
bigdatetime columns hold dates from January 1, 0001 to December 31, 9999 and 12:00:00.000000 a.m. to 11:59:59.999999 p.m.. Its storage size is 8 bytes. bigdatetime values are accurate to a microsecond. The internal representation of bigdatetime is a 64-bit integer containing the number of microseconds since 01/01/0000.
bigtime columns hold times from 12:00:00.000000 a.m. to 11:59:59.999999 p.m.. Its storage size is 8 bytes. The bigtime values are accurate to a microsecond. The internal representation of bigtime is a 64-bit integer containing the number of microseconds since midnight.
date is a literal value consisting of a date portion in single or double quotes. This column can hold dates between January 1, 0001 to December 31, 9999. Storage size is 4 bytes.
time is a literal value consisting of a time portion enclosed in single or double quotes. This column holds time from 12:00:00a.m. to 11:59:59:999p.m.. Storage size is 4 bytes.
Enclose date and time information in single or double quotes. You can enter it in either uppercase or lowercase letters and include spaces between data parts. Adaptive Server recognizes a wide variety of data entry formats, which are described in Chapter 7, “Adding, Changing, Transferring, and Deleting Data.” However, Adaptive Server rejects values such as 0 or 00/00/00, which are not recognized as dates.
The default display format for dates is “Apr 15 1987 10:23p.m.”. You can use the convert function for other formats. You can also perform some arithmetic calculations on datetime values with the built-in date functions, although Adaptive Server may round or truncate millisecond values, unless you use the time datatype.
For bigdatetime and bigtime, the value that appears reflects microsecond precision. bigdatetime and bigtime have default display formats that accomodate this increased precision.
hh:mi:ss.zzzzzzAM or PM
hh:mi:ss.zzzzzz
mon dd yyyy hh:mi:ss.zzzzzz
yyyy-mm-dd hh:mi:ss.zzzzzz
