The AT TIME ZONE clause was introduced in SQL Server 2016 to convert a date into a datetimeoffset value in a target time zone.

This function is similar to some other T-SQL functions, such as SWITCHOFFSET() and TODATETIMEOFFSET(), however, the AT TIME ZONE clause allows/(requires) you to specify the time zone offset by name, instead of an actual offset value.

This article explores how AT TIME ZONE works, and explains its benefits when compared to the other functions mentioned.

Example of Usage

Here’s a basic example of how the AT TIME ZONE clause works.

DECLARE @dto datetimeoffset = '2020-04-01 00:00:00.0000000 +00:00';
SELECT
  @dto AS [Original],
  @dto AT TIME ZONE 'New Zealand Standard Time' AS [NZ Time];

Result (using vertical output):

Original | 2020-04-01 00:00:00.0000000 +00:00
NZ Time  | 2020-04-01 13:00:00.0000000 +13:00

You might be wondering why Microsoft even introduced this feature when you could have used the SWITCHOFFSET() function to do the same thing?

Well, you can’t actually do the exact same thing with SWITCHOFFSET().

With SWITCHOFFSET(), you must provide the actual time zone offset either in the format [+|-]TZH:TZM or as a signed integer (for minutes). This means you need to know the exact time zone offset, and whether or not that time zone is currently observing daylight savings.

With the AT TIME ZONE clause, you don’t need to know that. All you need to know is the name of the time zone (and here’s how to get the time zone’s name).

Daylight Savings Example

Here’s an example that demonstrates the benefit of using AT TIME ZONE with regards to daylight savings.

DECLARE @dto1 datetimeoffset, @dto2 datetimeoffset;
SET @dto1 = '2020-04-01 00:00:00.0000000 +00:00';
SET @dto2 = '2020-04-07 00:00:00.0000000 +00:00';
SELECT
  @dto1 AS [@dto1],
  @dto2 AS [@dto2],
  @dto1 AT TIME ZONE 'New Zealand Standard Time' AS [NZ Time: @dto1],
  @dto2 AT TIME ZONE 'New Zealand Standard Time' AS [NZ Time: @dto2];

Result (using vertical output):

@dto1          | 2020-04-01 00:00:00.0000000 +00:00
@dto2          | 2020-04-07 00:00:00.0000000 +00:00
NZ Time: @dto1 | 2020-04-01 13:00:00.0000000 +13:00
NZ Time: @dto2 | 2020-04-07 12:00:00.0000000 +12:00

In New Zealand, daylight savings ends on March 5, 2020. Therefore in this example I use two dates (March 1 and March 7).

When I convert them to ‘New Zealand Standard Time’, AT TIME ZONE automatically includes daylight savings in its calculation and returns the applicable date/time.

So we can see that the March 1 date uses a time zone offset of +13:00 and the March 7 date uses +12:00 (because daylight savings ended on March 5).

If I had used SWITCHOFFSET() I would have had to have known which time zone offset to use for each date.

DECLARE @dto1 datetimeoffset, @dto2 datetimeoffset;
SET @dto1 = '2020-04-01 00:00:00.0000000 +00:00';
SET @dto2 = '2020-04-07 00:00:00.0000000 +00:00';
SELECT
  @dto1 AS [@dto1],
  @dto2 AS [@dto2],
  SWITCHOFFSET(@dto1, '+12:00') AS [+12:00],
  SWITCHOFFSET(@dto2, '+13:00') AS [+13:00];

Result (using vertical output):

@dto1  | 2020-04-01 00:00:00.0000000 +00:00
@dto2  | 2020-04-07 00:00:00.0000000 +00:00
+12:00 | 2020-04-01 12:00:00.0000000 +12:00
+13:00 | 2020-04-07 13:00:00.0000000 +13:00

Converting From Dates Without a Time Zone Offset

You can also use AT TIME ZONE on dates without a time zone offset. In fact, the function accepts any expression that can be resolved to a smalldatetime, datetime, datetime2, or datetimeoffset value.

However, when you do this, you need to be mindful of how the result is calculated. When the date is provided without offset information, the function applies the offset of the time zone assuming that the input date is in the target time zone. 

DECLARE @dt1 smalldatetime, @dt2 smalldatetime;
SET @dt1 = '2020-04-01 00:00:00';
SET @dt2 = '2020-04-07 00:00:00';
SELECT
  @dt1 AS [@dt1],
  @dt2 AS [@dt2],
  @dt1 AT TIME ZONE 'New Zealand Standard Time' AS [NZ Time: @dt1],
  @dt2 AT TIME ZONE 'New Zealand Standard Time' AS [NZ Time: @dt2];

Result:

@dt1          | 2020-04-01 00:00:00
@dt2          | 2020-04-07 00:00:00
NZ Time: @dt1 | 2020-04-01 00:00:00.0000000 +13:00
NZ Time: @dt2 | 2020-04-07 00:00:00.0000000 +12:00

Notice that although the time zone offsets were applied as specified, this didn’t affect the date/time. Both resulting date/times have the same value – only the time zone offset has changed.

If this is not what you want, then you can add AT TIME ZONE 'UTC' to the mix in order to first convert the original dates to UTC, before they’re converted to the desired time zone.

DECLARE @dt1 smalldatetime, @dt2 smalldatetime;
SET @dt1 = '2020-04-01 00:00:00';
SET @dt2 = '2020-04-07 00:00:00';
SELECT
  @dt1 AS [@dt1],
  @dt2 AS [@dt2],
  @dt1 AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' AS [NZ Time: @dt1],
  @dt2 AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' AS [NZ Time: @dt2];

Result:

@dt1          | 2020-04-01 00:00:00
@dt2          | 2020-04-07 00:00:00
NZ Time: @dt1 | 2020-04-01 13:00:00.0000000 +13:00
NZ Time: @dt2 | 2020-04-07 12:00:00.0000000 +12:00