SQL Server DATETIMEOFFSET

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

Summary: in this tutorial, you will learn how to use the SQL Server DATETIMEOFFSET data type to manipulate datetime with time zone.

Introduction to DATETIMEOFFSET data type #

The DATETIMEOFFSET allows you to manipulate any single point in time, which is a datetime value, along with an offset that specifies how much that datetime differs from UTC.

DATETIMEOFFSET syntax #

The syntax of the DATETIMEOFFSET is as follows:

DATETIMEOFFSET [ (fractional seconds precision) ]Copy

To declare a DATETIMEOFFSET variable, you use the following syntax:

DECLARE @dt DATETIMEOFFSET(7)Copy

To create a table column whose data type is DATETIMEOFFSET, you use the following form:

CREATE TABLE table_name (
    ...,
    column_name DATETIMEOFFSET(7)
    ...
);Copy

The DATETIMEOFFSET has a range from January 1, 1 CE to December 31, 999 CE. The time ranges from 00:00:00 through 23:59:59.9999999.

Literal formats #

The literal formats of DATETIMEOFFSET is as follows:

YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]Copy

For example:

2020-12-12 11:30:30.12345Copy

or by ISO

YYYY-MM-DDThh:mm:ss[.nnnnnnn]ZCopy

For example:

2020-12-12 19:30:30.12345Z.Copy

Time zone offset #

For a datetime or time value, a time zone offset specifies the zone offset from UTC. A time zone offset is represented as [+|-] hh:mm:

• hh is two digits that range from 00 to 14, which represents the number of hour in the time zone offset.
• mm is two digits that range from 00 to 59, which represents the number of additional minutes in the time zone offset.
• +(plus) or -(minus) specifies whether the time zone offset is added or subtracted from the UTC time to return the local time.

The valid range of a time zone offset is -14:00 to +14:00

DATETIMEOFFSET examples #

First, create a table named messages, which has a DATETIMEOFFSET column:

CREATE TABLE messages(
    id         INT PRIMARY KEY IDENTITY, 
    message    VARCHAR(255) NOT NULL, 
    created_at DATETIMEOFFSET NOT NULL
);Copy

Second, insert a new row with a DATETIMEOFFSET value into the messages table:

INSERT INTO messages(message,created_at)
VALUES('DATETIMEOFFSET demo',
        CAST('2019-02-28 01:45:00.0000000 -08:00' AS DATETIMEOFFSET));Copy

Third, query data from the messages table and use the AT TIME ZONE to convert the stored DATETIMEOFFSET value to  'SE Asia Standard Time' timezone.

SELECT 
    id, 
    message, 
	created_at 
        AS 'Pacific Standard Time'
    created_at AT TIME ZONE 'SE Asia Standard Time' 
        AS 'SE Asia Standard Time',
FROM 
    messages;Copy

Here is the output:

In this tutorial, you have learned how to use the DATETIMEOFFSET data type to manipulate the DATETIMEOFFSET value.