SQL Server DATETIMEOFFSET

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) ]
Code language: SQL (Structured Query Language) (sql)

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

DECLARE @dt DATETIMEOFFSET(7)
Code language: SQL (Structured Query Language) (sql)

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

CREATE TABLE table_name (
    ...,
    column_name DATETIMEOFFSET(7)
    ...
);
Code language: SQL (Structured Query Language) (sql)

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]
Code language: SQL (Structured Query Language) (sql)

For example:

2020-12-12 11:30:30.12345 
Code language: SQL (Structured Query Language) (sql)

or by ISO

YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z
Code language: SQL (Structured Query Language) (sql)

For example:

2020-12-12 19:30:30.12345Z.
Code language: SQL (Structured Query Language) (sql)

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
);
Code language: SQL (Structured Query Language) (sql)

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));
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

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