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:

SELECT inputdate AT TIME ZONE 'Name of destination time zone';

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.

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:

DECLARE @yyctime DATETIMEOFFSET = CAST('2018-09-05 09:00:00' AS DATETIME2(0)) AT TIME ZONE 'Mountain Standard Time';
SELECT @yyctime AS [TimeInCalgary];

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).

SELECT @yyctime AT TIME ZONE 'GMT Standard Time' AS [TimeInLondon];

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.