SQL server DateTimeOffset | Simple guide

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

DATETIMEOFFSETCopy Code

is as follows:

DATETIMEOFFSET [ (fractional seconds precision) ]Copy Code

To declare an

DATETIMEOFFSETCopy Code

variable, you use the syntax :

DECLARE @dt DATETIMEOFFSET(7)Copy Code

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

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

For every datatype, there will be a range function and the

DATETIMEOFFSETCopy Code

has a range from January 1, 1 CE to December 31, 999 CE. The zone ranges from

00:00:00Copy Code

over

23:59:59.9999999Copy Code

.

Formats for DateTimeOffset

We can define the formats of

DATETIMEOFFSETCopy Code

  as follows:

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

Let’s now see an example of the format:

2022-10-31 11:30:30.12345 Copy Code

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

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

2021-12-20 19:30:30.12345ZCopy Code

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

messagesCopy Code

, which has a 

DATETIMEOFFSETCopy Code

column:

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

now, insert a new row with a

DATETIMEOFFSETCopy Code

value to create

messagesCopy Code

table:

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

Finally, query data from the

messagesCopy Code

table and use the

AT TIME ZONECopy Code

for converting the stored

DATETIMEOFFSETCopy Code

value to 

'Indian Standard Time'Copy Code

timezone.

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

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

DATETIMEOFFSETCopy Code

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.