DATETIME data type

DATETIME is a domain, implemented as TIMESTAMP, used to store date and time of day information. DATETIME is a Transact-SQL type.

Syntax

DATETIME

The format in which DATETIME values are retrieved as strings by applications is controlled by the timestamp_format option setting. For example, the DATETIME value 2010/04/01T23:59:59.999999 can be returned to an application as 2010/04/01 23:59:59, or as April 1, 2010 23:59:59.999999 depending on the timestamp_format option setting.

A DATETIME value requires 8 bytes of storage.

Although the range of possible dates for the DATETIME data type is the same as the DATE type (covering years 0001 to 9999), the useful range of the DATETIME date type is from 1600-02-28 23:59:59 to 7911-01-01 00:00:00. Before and after this range, the hours and minutes portion of the DATETIME value is not retained.

Note

When the precision of the DATETIME value is reduced, built-in functions that pertain to minutes or seconds will produce meaningless results.

When a DATETIME value is converted to a DATETIMEOFFSET, the connection's time_zone_adjustment setting is used for the time zone offset in the result. In other words, the value is considered to be local to the connection. When a DATETIMEOFFSET value is converted to DATETIME, the offset is discarded.

Standards and compatibility

SQL/2008 Vendor extension.
Transact-SQL DATETIME, rather than TIMESTAMP, is used by Adaptive Server Enterprise. The DATETIME type in Adaptive Server Enterprise supports dates between January 1, 1753 and December 31, 9999 and supports less precision with the time portion of the value. In SQL Anywhere, DATETIME is implemented as a TIMESTAMP without these restrictions. You should be aware of these differences when migrating data between SQL Anywhere and Adaptive Server Enterprise.

Discuss this page in DocCommentXchange.