Checking Time Zones in SQL Server

I was building some basic queries around time zone manipulation and I am happy to say that I enjoyed myself as I found a way to get a time based on a geographic region.

This new feature is available to you from SQL 2016 which obviously means SQL database too (Azure). It is called AT TIME ZONE.

AT TIME ZONE implementation relies on following registry hive where time zones are installed: KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. This is exposed via the following query


SELECT * FROM sys.time_zone_info

Looking at books on line via https://msdn.microsoft.com/en-us/library/mt612790.aspx it is wrong.

Look at the screen shot, it states it is supported from SQL 2008 – well it is not, it only works on 2016.

times

Running it on a non SQL 2016 server you will get the following message:

Msg 208, Level 16, State 1, Line 1 Invalid object name ‘sys.time_zone_info’.

On a correct SQL version it will return the following. (Snippet)

times2

This information is key as we will use it to build the time zone queries (which is very basic).

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'GMT Standard Time' AS [My Location]
GO
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'US Mountain Standard Time' AS [US Mountain Standard Time]
GO
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Central Asia Standard Time' AS [Central Asia Standard Time]
GO
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Central Standard Time' AS [AUS Central Standard Time]

Based on the zone offset you will get the relevant correct time.

times3

 

4 thoughts on “Checking Time Zones in SQL Server

  1. Pingback: Checking Time Zones in SQL Server - SQL Server Blog - SQL Server - Toad World

  2. Hey Arun,

    Do you know if this is kept up to date automatically? And does it effectively synch to the TZ database maintained by IANA?

    https://www.iana.org/time-zones

    I ask, as various updates are issued per year by IANA and it would be good to know if SQL Server needs “manually maintaining” or if patches are issued to ensure conformity (and what the time lag might be). Unless this is the case, it could be dangerous to rely on these conversions as up to 10-15 updates are commonly issued per year…

    Cheers

    Will

    Like

    • Hi Will!
      My understanding is that the above is separate from IANA. its maintained solely by Microsoft (not sure how many updates per year) and are updated less frequently than IANA time zones

      Like

  3. Hello,I read your blogs named “Checking Time Zones in SQL Server | All About SQL” like every week.Your story-telling style is awesome, keep up the good work! And you can look our website about proxy free list.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s