Use datetime, smalldatetime, bigdatetime, bigtime, date, and time to store absolute date and time information. Use timestamp to store binary-type information.
Adaptive Server has various datatypes used to store date and time values.
date
time
smalldatetime
datetime
bigdatetime
bigtime
The default display format for dates is “Apr 15 1987 10:23PM”. bigdatetime/bigtime types have a default display format of “Apr 15 1987 10:23:00.000000PM” You can use the convert function for other styles of date display. You can also perform some arithmetic calculations on date and time values with the built-in date functions, though Adaptive Server may round or truncate millisecond 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. The last digit of the fractional second is always 0, 3, or 6. Other digits are rounded to one of these three digits, so 0 and 1 round to 0; 2, 3, and 4 round to 3; 5, 6, 7, and 8 round to 6; and 9 rounds to 10.. 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 AM to 11:59:59.999999 PM. Its storage size is 8 bytes. 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 AM to 11:59:59.999999 PM. Its storage size is 8 bytes. The internal representation of bigtime is a 64 bit integer containing the number of microseconds since midnight.
date columns hold dates from January 1, 0001 to December 31, 9999. Storage size is 4 bytes.
time is between 00:00:00:000 and 23:59:59:990. time values are accurate to 1/300 second. The last digit of the fractional second is always 0, 3, or 6. Other digits are rounded to one of these three digits, so 0 and 1 round to 0; 2, 3, and 4 round to 3; 5, 6, 7, and 8 round to 6; and 9 rounds to 10.You can use either military time or 12AM for noon and 12PM for midnight. A time value must contain either a colon or the AM or PM signifier. AM or PM may be in either uppercase or lowercase.
When entering date and time information, always enclose the time or date in single or double quotes.
