Adaptive Server allows datatypes datetime, smalldatetime, date, time, bigdatetime, and bigtime.
Display and entry formats for date and time data provide a wide range of date output formats, and recognize a variety of input formats. The display and entry formats are controlled separately. The default display format provides output that looks like “Apr 15 1997 10:23PM.” The convert command provides options to display seconds and milliseconds and to display the date with other date-part ordering. See Chapter 16, “Using Transact-SQL Functions in Queries,” for more information about displaying date values.
Adaptive Server recognizes a wide variety of data entry formats for dates. Case is always ignored, and spaces can occur anywhere between date parts. When you enter datetime and smalldatetime values, always enclose them in single or double quotes. Use single quotes if the quoted_identifier option is on; if you use double quotes, Adaptive Server treats the entry as an identifier.
Adaptive Server recognizes the two date and time portions of the data separately, so the time can precede or follow the date. Either portion can be omitted, in which case Adaptive Server uses the default. The default date and time is January 1, 1900, 12:00:00:000AM.
For datetime, the earliest date you can use is January 1, 1753; the latest is December 31, 9999. For smalldatetime, the earliest date you can use is January 1, 1900; the latest is June 6, 2079. For bigdatetime, the earliest date you can enter is January 1, 0001 and the latest is December 31, 9999. For date, the earliest date you can use is January 1, 0001; the latest is December 31, 9999. Dates earlier or later than these dates must be entered, stored, and manipulated as char, or unichar; or varchar or univarchar values. Adaptive Server rejects all values it cannot recognize as dates between those ranges.
For time, the earliest time is 12:00AM; the latest is 11:59:59:999. For bigtime, the earliest time is 12:00:00.000000AM; the latest is 11:59:59.999999PM.
