Converting Between Time Zones in SQL Server with AT TIME ZONE

Converting between time zones in SQL Server has evolved throughout the years. Prior to SQL Server 2016, there was no simple, built-in function to handle this task. We had to use a complex, multi-step approach involving functions like SWITCHOFFSET() and TODATETIMEOFFSET(), and we had to manually account for Daylight Saving Time (DST) rules for each time zone. This method was often prone to error and required constant maintenance to keep up with changing time zone and DST regulations.

SQL Server 2016 introduced AT TIME ZONE as the solution to this problem. This function simplifies the entire process by consolidating all the necessary logic into a single clause. It automatically handles the complexities of DST adjustments and time zone offsets by leveraging the server’s internal time zone information, which can be viewed in sys.time_zone_info.

As a result, AT TIME ZONE is now considered the most reliable method for converting between time zones in SQL Server, replacing the cumbersome and error-prone manual techniques that were previously necessary.

Syntax

The general syntax for the AT TIME ZONE function goes like this:

inputdate AT TIME ZONE 'timezone'

Here, inputdate is the date or time value you want to convert, and 'timezone' is the target time zone name. Valid time zone names are stored in the Windows Registry, but we can see them in SQL Server by querying the sys.time_zone_info view. The time zone names are case-sensitive and must exactly match the entries in sys.time_zone_info. A common mistake is to use abbreviations like PST instead of the full name 'Pacific Standard Time'.

Regarding the inputdate value, Microsoft’s documentation states:

When inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone. If inputdate is provided as a datetimeoffset value, then AT TIME ZONE clause converts it into the target time zone using the time zone conversion rules.

So we need to be aware of this with regards to the data type we use for the inputdate.

Case A: Input Without Offset Info (DATETIME or SMALLDATETIME)

Suppose you have a DATETIME value 2025-08-09 14:30:00 that you know is UTC, but the value itself contains no offset. When you run AT TIME ZONE 'UTC', SQL Server will assume that time is already in UTC and just attach the UTC offset:

DECLARE @UtcTimeNoOffset DATETIME = '2025-08-09 14:30:00';

SELECT 
    @UtcTimeNoOffset AS OriginalValue,
    @UtcTimeNoOffset AT TIME ZONE 'UTC' AS AttachUtcOffset,  -- Step 1: Treat as UTC
    (@UtcTimeNoOffset AT TIME ZONE 'UTC') AT TIME ZONE 'Pacific Standard Time' AS ConvertUtcToPacific; -- Step 2: Convert

Result:

OriginalValue           AttachUtcOffset                               ConvertUtcToPacific                          
----------------------- --------------------------------------------- ---------------------------------------------
2025-08-09 14:30:00.000                2025-08-09 14:30:00.000 +00:00                2025-08-09 07:30:00.000 -07:00

Here’s what happened:

1. @UtcTimeNoOffset is set to 2025-08-09 14:30:00 (no offset stored)
2. AT TIME ZONE 'UTC' results in 2025-08-09 14:30:00 +00:00 (offset info added, no change in clock time)
3. The second AT TIME ZONE results in the conversion rules being applied. This produces 2025-08-09 07:30:00 -07:00 (Pacific Daylight Time in August)

I picked UTC as an arbitrary time zone. This could just as easily have been any valid Windows time zone name.

The important thing is, when your input uses a date type without an offset, AT TIME ZONE 'Some Time Zone' will assume the date/time is already in that time zone and simply attach that zone’s offset.

So if we swap UTC for Eastern Standard Time, SQL Server will assume the original value was Eastern time and attach Eastern’s offset instead:

DECLARE @LocalTime DATETIME = '2025-08-09 14:30:00';

SELECT
    @LocalTime AS OriginalValue,
    @LocalTime AT TIME ZONE 'Eastern Standard Time' AS AttachEasternOffset,
    (@LocalTime AT TIME ZONE 'Eastern Standard Time') AT TIME ZONE 'Pacific Standard Time' AS EasternToPacific;

Output:

OriginalValue           AttachEasternOffset                           EasternToPacific                             
----------------------- --------------------------------------------- ---------------------------------------------
2025-08-09 14:30:00.000                2025-08-09 14:30:00.000 -04:00                2025-08-09 11:30:00.000 -07:00

What happened here:

1. We first attached the Eastern Standard Time offset (-04:00 in August due to daylight savings).
2. We then converted that date to Pacific Standard Time time (-07:00 in August).

So there was no need for us to explicitly state the time zone offsets. SQL Server worked all that out based on the time zone name.

Case B: Input With Offset Info (DATETIMEOFFSET)

Now suppose you already have a DATETIMEOFFSET value which explicitly tells SQL Server it’s UTC. When you use AT TIME ZONE, SQL Server will convert the time using time zone rules rather than just attaching an offset:

DECLARE @UtcTimeWithOffset DATETIMEOFFSET = '2025-08-09 14:30:00 +00:00';

SELECT 
    @UtcTimeWithOffset AS OriginalValueWithOffset,
    @UtcTimeWithOffset AT TIME ZONE 'Pacific Standard Time' AS ConvertUtcToPacific;

Result:

OriginalValueWithOffset                       ConvertUtcToPacific                          
--------------------------------------------- ---------------------------------------------
           2025-08-09 14:30:00.0000000 +00:00            2025-08-09 07:30:00.0000000 -07:00

What happens here:

1. @UtcTimeWithOffset is already aware of being UTC.
2. AT TIME ZONE 'Pacific Standard Time' directly applies the correct conversion, producing 2025-08-09 07:30:00 -07:00 (Pacific Daylight Time)

Here’s another example, this time starting with a different time zone:

DECLARE @EasternTimeWithOffset DATETIMEOFFSET = '2025-08-09 14:30:00 -04:00';

SELECT 
    @EasternTimeWithOffset AS OriginalValueWithOffset,
    @EasternTimeWithOffset AT TIME ZONE 'Pacific Standard Time' AS EasternToPacific;

Output:

OriginalValueWithOffset                       EasternToPacific                             
--------------------------------------------- ---------------------------------------------
           2025-08-09 14:30:00.0000000 -04:00            2025-08-09 11:30:00.0000000 -07:00

So pretty straightforward.

Understanding the Difference

So here’s a quick summary of the difference between providing an actual DATETIMEOFFSET value and a value without an offset (DATETIME, DATETIME2, or SMALLDATETIME):

• Without offset (DATETIME): First AT TIME ZONE just attaches the offset for the given time zone.
• With offset (DATETIMEOFFSET): AT TIME ZONE actually converts between time zones.