SQL SERVER – Get Current TimeZone Name in SQL Server

A very common question we developer often encounters is what is the current Timezone of the server where SQL Server is installed. It is very easy to get current Timezone of the server where SQL Server is installed. Here is the simple script for the same.

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone

As my server is in India it will display following results.

SQL SERVER - Get Current TimeZone Name in SQL Server timezonelist

I have been using this script for a quite a while and I have no idea of the origin of this script. Is there any other way to get the Timezone for SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
MySQL – When to Use TIMESTAMP or DATETIME – Difference Between TIMESTAMP or DATETIME
Next Post
MySQL – How to Detect Current Time Zone Name in MySQL

Related Posts

21 Comments. Leave new

  • Hi,

    we can also get time zone offset
    with following command.

    DECLARE @TZ SMALLINT
    SELECT @TZ=DATEPART(TZ, SYSDATETIMEOFFSET())
    SELECT CAST(@TZ/60 AS VARCHAR(5))+’:’+Cast(ABS(@TZ)%60 AS VARCHAR(5));

    Reply
  • I like this way of getting round the problem of no time zone name within SQL.

    Reply
  • Thank you Parth! I can always find answers *only* in user comments.

    Reply
  • Thanks much Dude. It helped me..

    Reply
  • SELECT DATEPART(TZOFFSET , SYSDATETIMEOFFSET()),
    DATENAME(TZOFFSET , SYSDATETIMEOFFSET())

    Reply
  • datediff(hh,sysutcdatetime(),getdate())

    Reply
  • Most of the time, I just need to normalize a datetime value (we have servers in all USA timezones). So, I use code similar to that which seantmanley provided above. Sometimes I will use the minutes offset, other times the hour. I’m in the USA, so I don’t need to worry about UTC offsets that are not whole hours.

    SELECT DateDiff(minute, GETUTCDATE(), GetDate()) AS TimeZoneOffsetMinutes
    , DateDiff(minute, GETUTCDATE(), GetDate()) / 60 AS TimeZoneOffsetHours

    If I need to worry about offsets that are not on a full hour boundary:

    SELECT DateDiff(minute, GETUTCDATE(), GetDate()) AS TimeZoneOffsetMinutes
    , Convert(decimal(5,2), DateDiff(minute, GETUTCDATE(), GetDate())) / Convert(decimal(5,2), 60) AS TimeZoneOffsetHours
    , Convert(decimal(5,2), Convert(decimal(5,2), DateDiff(minute, GETUTCDATE(), GetDate())) / Convert(decimal(5,2), 60)) AS TimeZoneOffsetHours2

    — Test code for fractional hours
    DECLARE @dt datetime2
    SET @dt = GetDate()
    –To verify this works with fractional offsets, uncomment the next line
    –SET @dt = DateAdd(minute, -30, @dt)
    SELECT DateDiff(minute, GETUTCDATE(), @dt) AS TimeZoneOffsetMinutes
    , Convert(decimal(5,2), DateDiff(minute, GETUTCDATE(), @dt)) / Convert(decimal(5,2), 60) AS TimeZoneOffsetHours
    , Convert(decimal(5,2), Convert(decimal(5,2), DateDiff(minute, GETUTCDATE(), @dt)) / Convert(decimal(5,2), 60)) AS TimeZoneOffsetHours

    Reply
  • what is Daylight Saving Time please tell me

    Reply
  • @jayesh, when I was in India I had the same question. Check this out: https://en.wikipedia.org/wiki/Daylight_saving_time

    Reply
  • The solutions in these comments only show the current timezone offset, not the timezone name. Getting the timezone name is important for handling timezones which observe daylight savings during Summer.

    For example if your server is running in New York’s timezone, then its offset at the moment is UTC+4, but if you are looking at a datetime value from last Christmas it would have been UTC+5 at that time. If you know the timezone name you can use AT TIME ZONE to handle this for you (requires SQL 2016+)

    https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-2017

    Reply
    • Right. However, how do I get that information when the registry stored procedure isn’t allowed?

      Reply
      • You can use CLR function to get TimeZone Name

      • Write a stored procedure to return the value using the registry stored procedure with an EXECUTE AS OWNER and have the DBA deploy it for you and then give the public (or at least you) the privs to execute it. It will allow it to be done without having to give you escalated privs to use it. Of course, a job running with SysAdmin privs won’t even need that.

  • Perfect! Thanks!

    Reply
  • Hi we are into health care domain over old server time showing web application example 5.02 PM recently we installed new data base server and installed same sql server 2008 R2 but in this server new server time showing 5:02 p.m health care web application you me out

    Configuration details
    Old server OS : windows server 20098 r2 ,sql server 2008 r2
    new server OS: windows server 2012 R2 , sql server 2008 r2

    Reply
  • i am facing below error Invalid object name ‘sys.time_zone_info’. while hit below query SELECT * FROM sys.time_zone_info

    Reply
  • Pinal,
    Thanks for the tip. This is the right tip because “AT TIME ZONE” requires it and it’s really (so far as I can see) the only foolproof way to resolve a UTC column with a mix of dates and times in and out of DST and not having to know the time zone and code for it up front. This will work for any time zone so long as it’s properly assigned in the server.

    Reply

Leave a Reply

Menu