DATETIMEOFFSET data type

The DATETIMEOFFSET data type is an alias for TIMESTAMP WITH TIME ZONE, used to store date, time of day, and time zone information.

Syntax

DATETIMEOFFSET

The DATETIMEOFFSET value contains the year, month, day, hour, minute, second, fraction of a second, and number of minutes before or after Coordinated Universal Time (UTC). The fraction is stored to 6 decimal places.

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

A DATETIMEOFFSET value requires 10 bytes of storage.

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

Do not use DATETIMEOFFSET for computed columns or in materialized views because the value of the governing time_zone_adjustment option varies between connections based on their location and the time of year.

Two DATETIMEOFFSET values are considered identical when they represent the same instant in UTC, regardless of the TIME ZONE offset applied. For example, the following statement returns Yes because the results are considered identical:

IF CAST('2009-07-15 08:00:00 -08:00' AS DATETIMEOFFSET) = 
CAST('2009-07-15 11:00:00 -05:00' AS DATETIMEOFFSET) THEN 
SELECT  'Yes'
ELSE
SELECT 'No'
END IF;

If you omit the time zone offset from a DATETIMEOFFSET value, it defaults to the current UTC offset of the client regardless of whether the timestamp represents a date and time in standard time or daylight time. For example, if the client is located in the Eastern Standard time zone and executes the following statement while daylight time is in effect, then a timestamp with a time zone appropriate for the Atlantic Standard time zone (-4 hours from UTC) will be returned.

SELECT CAST('2009/01/30 12:34:55' AS DATETIMEOFFSET);

The comparison of DATETIMEOFFSET values with timestamps without time zones is not recommended because the default time zone offset of the client varies with the geographic location of the client and with the time of the year.

Execute the following statement to determine the current time zone offset in minutes for a client:

SELECT CONNECTION_PROPERTY( 'TimeZoneAdjustment' );

Note

The TimeZoneAdjustment connection property is not supported in UltraLite databases.

Standards and compatibility

SQL/2008 The specific use of DATETIMEOFFSET is a vendor extension. To be compatible with SQL/2008, use TIMESTAMP WITH TIME ZONE. The TIMESTAMP WITH TIME ZONE type is optional SQL language feature F411 of the SQL/2008 standard.

Discuss this page in DocCommentXchange.