Let’s learn about how to use the SQL Server DateTimeOffset structure to manage DateTime in detail. Bobcares answers all questions and offers solutions big or small, as a part of our SQL Server Support.

Introduction to SQL server DateTimeOffset

The SQL Server DateTimeOffset allows you to manipulate any single point at a time, which is a DateTime value along with an offset that specifies how much that DateTime differs from time zone.

The SQL Server DateTimeOffset data type has the capability to store the date & time along with the Time Zone Offset. It is similar to both DateTime & DateTime2 data types, only that the DateTime & DateTime2 does not store the Time Zone Offset.to

Simple syntax for the DATETIMEOFFSET is as follows:

DATETIMEOFFSET [ (fractional seconds precision) ]

To declare an DATETIMEOFFSET variable, you use the syntax :

DECLARE @dt DATETIMEOFFSET(7)

We can create a table column with the data type “DATETIMEOFFSET” as:

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

For every datatype, there will be a range function and the DATETIMEOFFSET has a range from January 1, 1 CE to December 31, 999 CE. The zone ranges from 00:00:00 over 23:59:59.9999999.

Formats for DateTimeOffset

We can define the formats of DATETIMEOFFSET  as follows:

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

Let’s now see an example of the format:

2022-10-31 11:30:30.12345 

You can take a look at another example for “ISO”:

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

2021-12-20 19:30:30.12345Z

Time zone offset

A Time Zone Offset specifies the zone from UTC and differs between the local time & the Coordinated Universal Time (UTC). The timezone offset will be set as “[+|-] hh:mm:”

• Following “hh” has two digits that range from 00 to 14 which represents the number of hours in the time zone offset.

• mm having two digits ranging from 00 to 59, indicates the number of additional minutes in the time zone offset.

• Lastly, the mandatory sign for a time zone offset is “+ (plus) or – (minus)”, this shows whether the time zone offset is added or has been subtracted from the UTC time to obtain the local time. The valid range of time zone offset is from -14:00 to +14:00.

DateTimeOffset Examples

First, we need to 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
);

now, insert a new row with a DATETIMEOFFSET value to create messages table:

INSERT INTO messages(message,created_at)
VALUES('DATETIMEOFFSET demo',
        CAST('2021-05-20 01:45:00.0000000 -08:00' AS DATETIMEOFFSET));

Finally, query data from the messages table and use the AT TIME ZONE for converting the stored DATETIMEOFFSET value to  'Indian Standard Time' timezone.

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

Here is the final output:

[Need assistance with similar queries? We are here to help]

Conclusion

To conclude, from this guide you have learned how to use the SQL server DateTimeOffset data type to manipulate the DATETIMEOFFSET value, and convert Timezone. The SQL Server DateTimeOffset allows one to store a local date and time value which includes its time zone offset from the UTC time zone.