Bobcares

SQL server DateTimeOffset | Simple guide

by | Aug 17, 2022

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

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:

SQL server DateTimeOffset

 

[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.

PREVENT YOUR SERVER FROM CRASHING!

Never again
lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server
24/7 so that it remains lightning fast and secure.

GET STARTED

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Speed issues driving customers away?
We’ve got your back!