SQL Server DATETIMEOFFSET Data Type

Why Should We Use DATETIMEOFFSET?

DATETIMEOFFSET is a data type in SQL server that allows us to store precise dates and times along with the time zone effect. The time zone offset is represented in hours and minutes relative to UTC (Universal Time Coordinated) with a range of -14:00 to +14:00. DATETIMEOFFSET helps us to manage data that comes from different time zones. It helps us to maintain synchronization between the data from different time zones in our database.

Syntax:

DECLARE @MyDateTimeOffset DATETIMEOFFSET [ (fractional seconds precision) ]

You can replace @MyDateTimeOffset with your desired variable name. The optional value (fractional seconds precision) specifies the number of decimal places to store for the fractional seconds. The default value is 7 and the range lies from 0-7.

Syntax to Use DATETIMEOFFSET in a Table Column

CREATE TABLE MyTable (
  EventDateTime DATETIMEOFFSET(3)
)

The above statement will create a table Mytable with a column named EventDateTime that stores the time in DATETIMEOFFSET format with 3 decimal places of precision.

Supported String Literal Formats

We can store the data using the DATETIMEOFFSET in the following formats:

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

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

YYYY is the respected year. MM is the month and DD is the date .

hh is two digits that range from 00 to 14 and represent the number of hours in the time zone offset.

mm is two digits, that ranges from 00 to 59, that represent the number of additional minutes in the time zone offset.

+ (plus) or – (minus) is the mandatory sign for a time zone offset. This indicates whether the time zone offset is added or subtracted from the UTC time to obtain the local time. The valid range of time zone offset is from -14:00 to +14:00.