Dates and Times in SQL Server: AT TIME ZONE

Continuing the series on dates and times in SQL Server and Azure SQL Database, this week we look at the hint AT TIME ZONE.

In Azure SQL Database, the regional settings of the database are set to UTC by default. It is also advisable to store dates and times in UTC format on our on-premises SQL Server instances, and handle all time zone calculations at the presentation layer.

Sometimes though, it may be necessary to query data directly and see what the local date and time is, or calculate the date and time in another region. For this use-case we can use the AT TIME ZONE hint, which was introduced in SQL Server 2016.

How do we use it?

We can use this hint in a SELECT query, for example:

The accepted input data types are SMALLDATETIME, DATETIME and DATETIME2. The result is output as a DATETIMEOFFSET data type. If the input value is not in one of the accepted formats, we can convert it using the CAST or CONVERT system functions.

Where do we find the list of possible time zones?

The time zone name is taken from a list maintained in the following Windows registry hive: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones

Note: For SQL Server on Linux (and Docker containers), a registry shim performs the same function as the Windows registry by intercepting the API calls and returning the expected value(s).

We can also use a Transact-SQL (T-SQL) query against the system view sys.time_zone_info, which uses the information from the registry hive. This is the recommended method if you do not have access to the registry hive.

If there is a change to a time zone for any reason, Microsoft will update this list through a Windows Update or Cumulative Update, depending on the platform.

As of this writing (5 September 2018), the following time zones are available. Note that the results show the current offset from UTC time zone (when the query was run), as well as a BIT column representing the current Daylight Saving Time (DST) state.

name current_utc_offset is_currently_dst
Dateline Standard Time -12:00 0
UTC-11 -11:00 0
Aleutian Standard Time -09:00 1
Hawaiian Standard Time -10:00 0
Marquesas Standard Time -09:30 0
Alaskan Standard Time -08:00 1
UTC-09 -09:00 0
Pacific Standard Time (Mexico) -07:00 1
UTC-08 -08:00 0
Pacific Standard Time -07:00 1
US Mountain Standard Time -07:00 0
Mountain Standard Time (Mexico) -06:00 1
Mountain Standard Time -06:00 1
Central America Standard Time -06:00 0
Central Standard Time -05:00 1
Easter Island Standard Time -05:00 1
Central Standard Time (Mexico) -05:00 1
Canada Central Standard Time -06:00 0
SA Pacific Standard Time -05:00 0
Eastern Standard Time (Mexico) -05:00 0
Eastern Standard Time -04:00 1
Haiti Standard Time -04:00 1
Cuba Standard Time -04:00 1
US Eastern Standard Time -04:00 1
Paraguay Standard Time -04:00 0
Atlantic Standard Time -03:00 1
Venezuela Standard Time -04:00 0
Central Brazilian Standard Time -04:00 0
SA Western Standard Time -04:00 0
Pacific SA Standard Time -03:00 1
Turks And Caicos Standard Time -04:00 0
Newfoundland Standard Time -02:30 1
Tocantins Standard Time -03:00 0
E. South America Standard Time -03:00 0
SA Eastern Standard Time -03:00 0
Argentina Standard Time -03:00 0
Greenland Standard Time -02:00 1
Montevideo Standard Time -03:00 0
Magallanes Standard Time -03:00 0
Saint Pierre Standard Time -02:00 1
Bahia Standard Time -03:00 0
UTC-02 -02:00 0
Mid-Atlantic Standard Time -01:00 1
Azores Standard Time +00:00 1
Cape Verde Standard Time -01:00 0
UTC +00:00 0
Morocco Standard Time +01:00 1
GMT Standard Time +01:00 1
Greenwich Standard Time +00:00 0
W. Europe Standard Time +02:00 1
Central Europe Standard Time +02:00 1
Romance Standard Time +02:00 1
Central European Standard Time +02:00 1
W. Central Africa Standard Time +01:00 0
Namibia Standard Time +01:00 0
Jordan Standard Time +03:00 1
GTB Standard Time +03:00 1
Middle East Standard Time +03:00 1
Egypt Standard Time +02:00 0
E. Europe Standard Time +03:00 1
Syria Standard Time +03:00 1
West Bank Standard Time +03:00 1
South Africa Standard Time +02:00 0
FLE Standard Time +03:00 1
Israel Standard Time +03:00 1
Kaliningrad Standard Time +02:00 0
Libya Standard Time +02:00 0
Arabic Standard Time +03:00 0
Turkey Standard Time +03:00 0
Arab Standard Time +03:00 0
Belarus Standard Time +03:00 0
Russian Standard Time +03:00 0
E. Africa Standard Time +03:00 0
Iran Standard Time +04:30 1
Arabian Standard Time +04:00 0
Astrakhan Standard Time +04:00 0
Azerbaijan Standard Time +04:00 0
Russia Time Zone 3 +04:00 0
Mauritius Standard Time +04:00 0
Saratov Standard Time +04:00 0
Georgian Standard Time +04:00 0
Caucasus Standard Time +04:00 0
Afghanistan Standard Time +04:30 0
West Asia Standard Time +05:00 0
Ekaterinburg Standard Time +05:00 0
Pakistan Standard Time +05:00 0
India Standard Time +05:30 0
Sri Lanka Standard Time +05:30 0
Nepal Standard Time +05:45 0
Central Asia Standard Time +06:00 0
Bangladesh Standard Time +06:00 0
Omsk Standard Time +06:00 0
Myanmar Standard Time +06:30 0
SE Asia Standard Time +07:00 0
Altai Standard Time +07:00 0
W. Mongolia Standard Time +07:00 0
North Asia Standard Time +07:00 0
N. Central Asia Standard Time +07:00 0
Tomsk Standard Time +07:00 0
China Standard Time +08:00 0
North Asia East Standard Time +08:00 0
Singapore Standard Time +08:00 0
W. Australia Standard Time +08:00 0
Taipei Standard Time +08:00 0
Ulaanbaatar Standard Time +08:00 0
North Korea Standard Time +08:30 0
Aus Central W. Standard Time +08:45 0
Transbaikal Standard Time +09:00 0
Tokyo Standard Time +09:00 0
Korea Standard Time +09:00 0
Yakutsk Standard Time +09:00 0
Cen. Australia Standard Time +09:30 0
AUS Central Standard Time +09:30 0
E. Australia Standard Time +10:00 0
AUS Eastern Standard Time +10:00 0
West Pacific Standard Time +10:00 0
Tasmania Standard Time +10:00 0
Vladivostok Standard Time +10:00 0
Lord Howe Standard Time +10:30 0
Bougainville Standard Time +11:00 0
Russia Time Zone 10 +11:00 0
Magadan Standard Time +11:00 0
Norfolk Standard Time +11:00 0
Sakhalin Standard Time +11:00 0
Central Pacific Standard Time +11:00 0
Russia Time Zone 11 +12:00 0
New Zealand Standard Time +12:00 0
UTC+12 +12:00 0
Fiji Standard Time +12:00 0
Kamchatka Standard Time +13:00 1
Chatham Islands Standard Time +12:45 0
UTC+13 +13:00 0
Tonga Standard Time +13:00 0
Samoa Standard Time +13:00 0
Line Islands Standard Time +14:00 0

Let’s shake things up a bit. Say that we want to know what the local time is in London, if it is 9am in Calgary on today, 5 September 2018, but we don’t know whether it’s still Daylight Saving Time in Calgary.

Let’s get the local time in Calgary:

The result will be returned as a DATETIMEOFFSET data type: 2018-09-05 09:00:00 -06:00.

Now we can take the result of that and use it to see what the equivalent time is in GMT (which is one hour ahead of UTC during summer).

As expected, the result is 2018-09-05 16:00:00 +01:00.

When is this useful?

There is a period of time every year when the Daylight Saving Time changeover lags by a week depending on where you are in the world. The AT TIME ZONE hint is very useful when trying to figure out when something actually happened, especially during that lag time.

If you have even better use-cases for this hint, feel free to leave a comment below.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: