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.
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)
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));
Thanks very much
I like this way of getting round the problem of no time zone name within SQL.
Thank you Parth! I can always find answers *only* in user comments.
Thanks much Dude. It helped me..
SELECT DATEPART(TZOFFSET , SYSDATETIMEOFFSET()),
DATENAME(TZOFFSET , SYSDATETIMEOFFSET())
Thanks @fausto
datediff(hh,sysutcdatetime(),getdate())
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
what is Daylight Saving Time please tell me
graja has already replied before me.
@jayesh, when I was in India I had the same question. Check this out: https://en.wikipedia.org/wiki/Daylight_saving_time
Thanks for sharing.
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
Right. However, how do I get that information when the registry stored procedure isn’t allowed?
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.
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
i am facing below error Invalid object name ‘sys.time_zone_info’. while hit below query SELECT * FROM sys.time_zone_info
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.