question

ssalun avatar image
1 Vote"
ssalun asked MelissaMa-msft edited

Calculate Timezoneoffset using TimezoneName

Hi All,

I am dealing with many timezones , I have a required to generate a SSRS report which uses the timezoneoffset based on the timezone name. The offset value returned should consider with or without DayLight Saving Time.

The input to the report is the date.

Can anyone please help me with the function to which i can pass date and it will return offset.

Input
Date: 2021-08-27
TimeZoneName- America/Chicago or Europe/Berlin

Output: Timezoneoffset -05/+1

Thanks.

sql-server-generalsql-server-transact-sqlsql-server-reporting-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe you can imply some C# or VB code? Then you can use a publicly available XML file with time zones, build a TimeZoneInfo object and call its GetUtcOffset. Or use some third-party libraries.


0 Votes 0 ·

Hi @SwapnilSalunke-4731,

Could you please provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

In T-SQL, you can do as in this example_

DECLARE @tz nvarchar(100) = 'Pacific Standard Time'
SELECT datepart(tzoffset, sysutcdatetime() AT TIME ZONE @tz)

However, this requires that the names you have agree with those in the Windows registry, which is less likely. Rather you would have to Google around for a library to parse time-zone names, and all depending the source, you have to augment the solution you find.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ErlandSommarskog ,

Thanks for your comment , but the sql server version i am using is 2012 not 2016 or later .I think the "AT TIME ZONE " will not work.

0 Votes 0 ·

So that's a lesson to next time: include which version of SQL Server, you are working with.

Anyway, the gist of what I said is that it probably would not work anyway.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @SwapnilSalunke-4731,

Welcome to Microsoft Q&A!

Unfortunately SQL Server doesn't yet support IANA time zone identifiers (like America/Chicago) directly.

For now, the best option is to convert from the IANA identifier to a corresponding Windows time zone identifier in your application layer, and store that in your SQL Server.

Since your version is SQL Server 2012 which "AT TIME ZONE " does not work, you could refer below which is an alternative approach.

 -- all SQL Server versions
 declare @utc_date datetime = getdate()
 select @utc_date as utc_time_zone, 
   dateadd(hh, datediff(hh, getutcdate(), getdate()), @utc_date) as local_time_zone

In addition, it is also recommended to create and use a Calendar table.

Aaron Bertrand covers this exact scenario in depth here. You could refer it and find out more solutions.

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.