AT TIME ZONE Is On Your Side

Dealing with time zones in general can be a headache. Thankfully, SQL Server 2016 included a new clause to make working with time zones more manageable. Let’s look at a couple examples:

I’m in Eastern Standard Time so we’ll start here. Keep in mind that when no offset information is included, SQL Server is going to assume that the date value is in the target time zone.

That looks familiar. What if we wanted to convert that time to Pacific Standard Time? We could run the following with AT TIME ZONE:

Our time value looks the same except we now see -08:00 at the end. That addition is to indicate the hours behind coordinated universal time (UTC). We can confirm that by checking what is stored in sys.time_zone_info:

SELECT * FROM sys.time_zone_info
WHERE name = 'Pacific Standard Time'

If we want to see the time value reflect the difference between Eastern Standard Time and Pacific Standard Time, we add an additional AT TIME ZONE like this:

Now we see 04:00 which is our Pacific Standard Time compared to our original 07:00 Eastern Standard Time.

How does this work with dreaded daylight saving time?

We can test that, too.

Daylight saving time in 2021 is set to begin at 2:00 AM on March 14 in Ohio. Sticking with comparing our time to Pacific Standard Time, if we check the time at 1:00 AM on March 14 we’ll see a value similar to before:

We have our -08:00 indicating the time has been converted to Pacific Standard Time. What will happen once we reach 2:00 AM and daylight saving time begins?

Now our value looks a little bit different. We no longer have -08:00 but have -07:00 due to losing an hour. The time itself has also been changed to 03:00 instead of 02:00.

Hopefully, this isn’t something you have to mess with too often but if you do, it’s nice if your times across databases are all stored in UTC. If you have some times stored in UTC but others stored in the time zone they came from…good luck.

Thanks for reading!