AT Time Zone – Convert timezone directly using SQL

Globalization brings new opportunities and enhance economic growth to any business, but along with all the goodness it also brings complexity to manage different time zones. One has to be always on toes to match different times. but what if you’ve got an application\code where you have to convert timezone within SQL Server data? If you are using SQL Azure Databases or SQL Server 2016 you don’t have to worry about this trouble because Microsoft introduced this brand new function to make things easy for you: AT TIME ZONE;

AT Time Zone

This function converts an inputdate to target time zone as per your wish, isn’t it cool. I am loving it, it simplified too many things. Now I don’t have to write code\use dlls to convert date time – DST was the biggest issue and this function simply takes care of DST on its own.

The syntax is quite simple:

inputdate AT TIME ZONE timezone

Inputdate: is just a date no fancy thing (I recommend providing date time along with offset value to get best results)

Timezone: mention the target time zone to which you want SQL to convert your input datetime.

 

How SQL Server convert the timezone? Is it possible to convert to any timezone using SQL?

This function uses windows mechanism to convert datetime value across different time zones. SQL Server picks the time zones that are stored in Registry. All time zones installed on the computer can be found in this registry hive:

KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones

For SQL users there is a catalog view sys.time_zone_info, (you’ll be surprised to know that this view was there since SQL 2008) which lists down all available time zones & you can convert date time into any of these available time zones.

 

Do you see any difference while using this function in SQL Azure Databases or SQL Server 2016?

Yes, since At Time Zone is dependent on the available time zones I see some variation. The catalog view sys.time_zone_info lists down 109 time zones for a V12 SQL Azure Database while the same catalog view on SQL Server 2016 CTP3 returns 113 time zones. In short if you are on SQL Azure Database (as of writing this blog 1-June-2016) you won’t be able to convert your time zone to 4 such time zones which are available on SQL Server 2016 CTP3.

I know you must be wondering what were those time zones: well here is the list of those time zones which are not avialable (as of now) on Azure Databases:

Name

Altai Standard Time

Astrakhan Standard Time

Sakhalin Standard Time

Transbaikal Standard Time

 

What if DST is enabled?

AT TIME ZONE applies specific rules for converting datetime values that fall into an interval that is affected by the DST change. The catalog view is DST aware and keeps a track of the same, here is a small screenshot to show the same:

 

Let’s see how it works:

Here are the queries I used to test it:

select getdate()

go

SELECT CONVERT(datetimeoffset, ‘2016-05-31 16:56:00.253 +5:30’)

AT TIME ZONE ‘Eastern Standard Time’;

go

SELECT CONVERT(datetimeoffset, ‘2016-05-31 16:56:00.253 +5:30’)

AT TIME ZONE ‘Central Standard Time’;

 

Hope you liked the post.

Happy Learning!

feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLChamp