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
Copy Code
is as follows:
DATETIMEOFFSET [ (fractional seconds precision) ]
Copy Code
To declare an
DATETIMEOFFSET
Copy 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
DATETIMEOFFSET
Copy Code
has a range from January 1, 1 CE to December 31, 999 CE. The zone ranges from 00:00:00
Copy Code
over 23:59:59.9999999
Copy Code
.
Formats for DateTimeOffset
We can define the formats of
DATETIMEOFFSET
Copy 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]Z
Copy Code
2021-12-20 19:30:30.12345Z
Copy 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
messages
Copy Code
, which has a DATETIMEOFFSET
Copy 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
DATETIMEOFFSET
Copy Code
value to create messages
Copy 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
messages
Copy Code
table and use the AT TIME ZONE
Copy Code
for converting the stored DATETIMEOFFSET
Copy 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
DATETIMEOFFSET
Copy 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.
0 Comments