TIMESTAMP data type

The TIMESTAMP data type is used to store a point in time containing the year, month, day, hour, minute, second, and fraction of a second.

Syntax

TIMESTAMP

Remarks

The TIMESTAMP value contains the year, month, day, hour, minute, second, and fraction of a second. The fraction is stored to 6 decimal places.

The format in which TIMESTAMP values are retrieved as strings by applications is controlled by the timestamp_format option setting. For example, the TIMESTAMP 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 TIMESTAMP value requires 8 bytes of storage.

Although the range of possible dates for the TIMESTAMP data type is the same as the DATE type (covering years 0001 to 9999), the useful range of TIMESTAMP date types 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 TIMESTAMP value is not retained.

Note

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

When a TIMESTAMP value is converted to TIMESTAMP WITH TIME ZONE, 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 TIMESTAMP WITH TIME ZONE value is converted to TIMESTAMP, the offset is discarded.

Standards and compatibility

SQL/2008 Compatible with SQL/2008.
Transact-SQL Adaptive Server Enterprise uses the DATETIME type for TIMESTAMP values.

Discuss this page in DocCommentXchange.