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.
Other Ways to Check the Current TimeZone
The registry script works well, but keep in mind that xp_regread is an undocumented extended procedure. It usually needs high permissions, and extended procedures are not available on Azure SQL Database. If the script fails for a login, permission is the first thing I check.
If you only need the offset from UTC, there is a documented way. SELECT SYSDATETIMEOFFSET() returns the server date and time along with the offset, and SELECT DATENAME(TZOFFSET, SYSDATETIMEOFFSET()) returns just the offset, such as +05:30 on a server in India. Newer versions of SQL Server also have a CURRENT_TIMEZONE() function that returns the zone as text, so check the documentation for your version before you rely on it.
Remember that an offset and a time zone name are not the same thing. Many zones move their clocks for daylight saving, so the offset can change during the year while the name stays the same. That is why the name from the registry is often more useful for reports than a single number.
Also, this is the time zone of the Windows machine that runs SQL Server, not of the person who runs the query. A user in London and a user in Mumbai get the same answer from the same server. If your users live in many countries, I prefer to store times in UTC with SYSUTCDATETIME() and convert them to local time only when I show them on the screen. It keeps the data simple and avoids a lot of confusion later.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.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.
Perfect! Thanks!
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.