SQL Server DATETIMEOFFSET

November 10, 2022 | by Arround The Web | No comments

“In this article, we will learn the various properties and the usage of the DATETIMEOFFSET data type in SQL Server. This data type allows you to define a date value that combines the time section and the time zone based on UTC or GMT.”

Let’s explore.

Syntax

The following is the syntax of the DATETIMEOFFSET value in SQL Server.

DATETIMEOFFSET [ (fractional seconds precision) ]

The fraction seconds precision argument is optional.

Usage

There are two main ways you can use the DATETIMEOFFSET type.

DECLARE @var datetimeoffset(7);
CREATE TABLE table_name(col datetimeoffset(7));

The datetimeoffset value supports the following ranges:

Date – 0001-01-01 to 9999-12-31
Time – 00:00:00 to 23:59:59.9999999

Literal Formats

The datetimeoffset value follows a format as shown:

YYYY-MM-DD hh:mm:ss {+|-}hh:mm

The following are some properties of the datetimeoffset you should know:

It supports user-defined second precision.
It has an accuracy of 100 nanoseconds.
Storage size of 10 bytes.
It is not daylight saving aware
Uses Gregorian calendar

The following table shows the precision scale for the datetimeoffset value.

Example Usage

The following shows the usage of the datetimeoffset type.

declare @var datetimeoffset(7) = '10-10-22 08:00:02 +03:00';
select @var as dt;

Output:

dt
----------------------------------
2022-10-10 08:00:02.0000000 +03:00

(1 row affected)

We can also use the datetimeoffset type in a table column as:

create table t(
id int identity primary key,
n varchar(10),
time datetimeoffset(7)
);
insert into t(n, time) values ('Linuxhint', '10-10-22 08:00:02 +03:00');

Conclusion

In this post, you learned the various concepts about the datetimeoffset data type in SQL Server. You also learned how to use it in variable definition and table creation.

Source: linuxhint.com