SQL Server DATETIMEOFFSET Data Type

When we are working with different times and dates in the SQL server we struggle with the different time zones. In this article, we are going to explore the data type DATETIMEOFFSET in SQL Server which helps us to resolve this problem. But before you deep dive into this article make sure you are familiar with the basics of SQL Server and data types like DATETIME and DATETIME2.

SQL Server DATETIMEOFFSET Data Type

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.

Why Should We Use DATETIMEOFFSET?

The DATETIMEOFFSET data type offers a variety of applications. Following are the reasons we should use DATETIMEOFFSET in our databases:

• It provides accurate time tracking across various time zones.

• It provides global application support. Must be used when we are handling Flight schedules, global communication supports, customer orders across different time zones.

• It has larger a date range than the DATETIME datatype. DATETIMEOFFSET offers range from January 1, 0001 to December 31, 9999.

• Provides an accuracy of 100 nanoseconds which is way better than the other data types.

• Provides decimal precision up to 7 digits.

Examples of DATETIMEOFFSET

1. Store a Timestamp with Time Zone Offset

DECLARE @OrderTime DATETIMEOFFSET = '2024-05-10 13:45:00.0000000 -08:00';

Explanation: The above query will store an order time in Pacific Standard Time (PST) 8 hours behind UTC.

Output:

2. Convert DATETIMEOFFSET Data Type to Other Date and Time Types.

DECLARE @datetimeoffset datetimeoffset(4) = '12-12-2 12:32:10 +01:00';  
DECLARE @date date= @datetimeoffset;  
  
SELECT @datetimeoffset AS '@Gfgtimeoffset ', @date AS 'date';  

Explanation: The above query creates a datetimeoffset variable then extracts the date part from it and then assign the date to a separate variable and then it displays both the values.

Output:

Output

3. Creating a Table with DATETIMEOFFSET

CREATE TABLE EventSchedule (
    EventID INT PRIMARY KEY,
    EventName NVARCHAR(100),
    EventDateTime DATETIMEOFFSET
);

INSERT INTO EventSchedule VALUES
(1, 'Conference', '2023-01-15T08:00:00-05:00'),
(2, 'Webinar', '2023-02-20T15:30:00+02:00');

Explanation: In the following example we have created a table called EventSchedule with three columns including EventDateTime which stores the data in DATETIMEOFFSET format. Then we have added two events with their dates, times and time zone offsets.

Output:

Output

4. Querying with DATETIMEOFFSET

-- Retrieve events that occur after a specific date and time
SELECT *
FROM EventSchedule
WHERE EventDateTime > '2023-02-01T00:00:00+00:00';

Explanation: In the above example we have executed a query that fetches events happening after the second month of 2023 regardless of their time zones.

Output:

Output

5. Convert DATETIMEOFFSET Value to a Different Time Zone

First create the following table with a DATETIMEOFFSET column.

CREATE TABLE events(
    unique_id         INT PRIMARY KEY IDENTITY, 
    description    VARCHAR(255) NOT NULL, 
    event_time DATETIMEOFFSET NOT NULL
);

Insert dummy data into the table:

INSERT INTO events(description,event_time)
VALUES('GeeksforGeeks meet ',
        CAST('2023-12-12 08:45:00.0000000 -08:00' AS DATETIMEOFFSET));

Now execute the following query to convert the standard Pacific timezone to Asisan Standard Timezone.

SELECT 
    unique_id, 
    description, 
    event_time AS 'Pacific Standard Time',
    event_time AT TIME ZONE 'SE Asia Standard Time' AS 'SE Asia Standard Time'
FROM 
    events;

Output:

Ouput

Conclusion

In this article we have leaned about the DATETIMEOFFSET in SQL server and why it is used to avoid the timezone conflicts. We hope that this article would have helped you to understand the DATETIMEOFFSET properly. Remember in order to master these datatypes practise is the key. Try to practise the above examples on your own to get a better understand about how this data type works.