SQL Server datetimeoffset for Time Zones

By: Eric Blinn | Updated: 2022-03-14 | Comments | Related: > Dates

Problem

Microsoft SQL Server 2016 and later has a new data type, datetimeoffset, and feature set related to time zones. I need to know how to use it these features. I also need to learn how I can apply this to legacy systems that were built without the new data type.

Solution

This tip will explain how to use the new data type and convert values of the type to other time zones with T-SQL code. It will also show how to use the time zone conversion clauses on legacy data types such as smalldatetime, datetime, and datetime2. While all of the examples shown in this tip will be of column values being modified and converted, the same code would work on variables of these types.

All of the demos in this tip were run against Microsoft SQL Server 2019.

SQL Server datetimeoffset Data Type

The SQL Server datetimeoffset datatype acts exactly like datetime2 except that it includes time zone offset information for the time when compared to UTC time. It uses 10 bytes of storage where datetime2 uses 6, 7, or 8 depending on the precision chosen.

This short script will create a column of datetime2 and another of datetime offset. Here is the syntax:

CREATE TABLE #DateTests (DateTime_2 DATETIME2(7), DateTime_Offset DATETIMEOFFSET(7));

INSERT INTO #DateTests VALUES (GETDATE(), GETDATE());

SELECT * FROM #DateTests;

Notice that the only difference in the output is that the second column has extra numbers at the end to account for the time zone offset. Since the GETDATE() function returns a datetime – not a datetimeoffset -- which does not include time zone information the column records a zero for the offset.

How to add a valid time zone to a datetime value

Continuing the previous example, the datetimeoffset column is not recording an accurate time reading for this author. That query was executed at 20:06 Eastern Time in the US, not in UTC (+00:00). To get that query to record the correct time there are 2 options.

The first option is to inform SQL Server which time zone the value should be assigned to. This is done by using the AT TIME ZONE key phrase. AT TIME ZONE will assign a time zone offset to a datetime, smalldatetime, or datetime2 value that otherwise would not include one. It works by simply adding the words "AT TIME ZONE" immediately after a datetime or datetime2 value and then listing a valid time zone. This time zone is a NVARCHAR(256) type. Since this author is writing this tip from the Eastern Time zone in the US, that will be the time zone selected.

The second option is to use the GETUTCDATE() function which will return the UTC date and time rather than the system date and time. This will be recorded with the correct time zone (+00:00) and will thus be accurate.

DROP TABLE IF EXISTS #DateTests;

CREATE TABLE #DateTests (DateTime_2 DATETIME2(7), DateTime_Offset DATETIMEOFFSET(7));

INSERT INTO #DateTests VALUES (GETDATE(), GETDATE() AT TIME ZONE N'US Eastern Standard Time');

INSERT INTO #DateTests VALUES (GETDATE(), GETUTCDATE());

SELECT * FROM #DateTests;

While these values are different, they are both accurate representations of the time that this action took place.

This query output shows 2 rows. They have different dates and times along with different time zone offsets.

Getting a list of valid Time Zones

In order to use the AT TIME ZONE key phrase a target time zone must be chosen by name. In the previous example the time zone chosen was "'US Eastern Standard Time". A complete list of available time zones can be found by running this query. The AT TIME ZONE function will accept a hard coded value -- as seen in the previous example -- or a variable or column of the appropriate type.

SELECT * FROM sys.time_zone_info;

Using AT TIME ZONE to change time zones

Now that the temporary table has 2 rows with valid values for the date, time, and time zone offset, these values can be easily converted to any other valid time zone by, again, using the AT TIME ZONE key phrase. AT TIME ZONE works exactly the same way as before except that now the input value already has a time zone in it. This means that the function won't look to add time zone information, but rather convert that date and time value from its current time zone to another.

This query will run the AT TIME ZONE conversion against the DateTime_Offset column of the temporary table which uses the datetimeoffset data type and therefore already has a time zone associated to it. By utilizing a cross join the conversion will be run against every single time zone in the dynamic management object, time_zone_info.

SELECT 
    DateTime_Offset
  , tzi.name
  , DateTime_Offset AT TIME ZONE tzi.name AS ConvertedDateAndTime
FROM #DateTests
  CROSS JOIN sys.time_zone_info tzi
ORDER BY tzi.name;

In these query results each pair of input values, when converted to another time zone, returns the same date, time, and time zone. The confirms the earlier assertion that the 2 different values were equally valid.

This snippet of the results of the prior query show that each pair of dates, times, and time zone offsets, when converted to the same new time zone, calculate as the same new value.

Converting legacy data types between time zones

As seen in the previous example, a datetime value needs to have a time zone associated to it in order to be converted to another time zone using the AT TIME ZONE key phrase. At the same time, a legacy datetime value that doesn't already have a time zone associated to it can have one added using that same AT TIME ZONE function. So how does one do both steps in a single statement, perhaps to convert a legacy smalldatetime, datetime, or datetime2 value from an original time zone to a new time zone? The answer is a very strange looking SQL statement that uses 2 AT TIME ZONE key phrases right next to each other. The first associates a time zone to the datetime value that doesn't already have one and the second converts that value to the new time zone. It looks like this.

DROP TABLE IF EXISTS #DateTests;

CREATE TABLE #DateTests (DateTime_2 DATETIME2(7));

INSERT INTO #DateTests VALUES (GETDATE());
 
SELECT DateTime_2 AT TIME ZONE 'US Eastern Standard Time' AT TIME ZONE 'India Standard Time' AS MySystemTimeInIndia
FROM #DateTests

Since this author's computer remains in Eastern Time in the US, that is the first time zone listed in an AT TIME ZONE function and associates the -5 UTC offset to the output of the GETDATE function. The second AT TIME ZONE converts that output to the current time in India Standard Time. The output can be seen below.

Final Thoughts

The idea of storing dates and times with a time zone offset is relatively new to SQL Server and is often underutilized in applications. One can hope that it becomes more commonplace as more SQL developers learn about it and the ease in which it can be implemented.

Next Steps

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-03-14

Understanding Time Zones in SQL Server