How to get Current Time Zone Information of SQL Server

Problem: How to get Current Time Zone Information of SQL Server.

Solution: 

Xp_regread Extended Stored procedures for different versions of SQL Server.

SQL Server does not store the time zone information by itself, it grabs the windows time where it has been used, to get the time zone information of where SQL Server is installed. We have to get into the Registry to see the time zone.

To see where registry resides on your computer, you can run regedit from Command prompt.

KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\TimeZoneInformation.

 Please see the below image to get a preview of the registry timezone information.

 sys.time_zone_info starting SQL Server 2016.

NOTE:

 Xp_regread Extended stored procedures are not recommended to be used by Microsoft, sometimes you may get access denied to this stored procedure as well.

Example:

In this article I am going to show you how we can retrieve the time zone information.

Example1:

Using Xp_regread we can navigate to the folders inside the registry and bring back the output of Time zone name.

DECLARE @TimeZone VARCHAR(50), @daylighttime VARBINARY(50)

EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',

'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',

'TimeZoneKeyName',@TimeZone OUT

SELECT @TimeZone

EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',

'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',

'DynamicDaylightTimeDisabled',@daylighttime OUT

SELECT @daylighttime

OUTPUT:

 

Example 2:

In SQL Server 2016 they have created a system view which can show us all the time zone names, day light savings is active or not, time offset.

--Returns information about supported time zones. All time zones installed on the computer are stored in the following registry hive:

SELECT * FROM SYS.TIME_ZONE_INFO

OUTPUT: