Convert IANA TimeZone name to UTC / GMT?

[h4tt3n]

Hello,

Using Transact-SQL, is there a way to convert an IANA time zone name string to a GMT or UTC offset?

I have access to customer timezone data only as an IANA tz formatted string in the form of eg. Europe/Paris or America/New_York, and I need to convert this string into a number with which I can offset a datetime column to represent customer local time.

The function AT TIME ZONE apparently won't work in this case.

Thanks in advance!

[V-lianl-msft]

Hi @h4tt3n ,

Unfortunately, there is no good solution for this kind of issue, but there are some alternatives.

In my opinion, I'd like to suggest you add a location table to collection these locations and group them to different timezone country groups.
After these steps, you can use above table to accurate mapping records and modify them with their timezone group offset.

Please refer to these thread:https://stackoverflow.com/questions/57261944/apply-timezone-by-id-on-power-bi 

https://community.powerbi.com/t5/Desktop/Convert-UTC-to-timezones/m-p/354322 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[h4tt3n]

@V-lianl-msft

I suspected that I might have to build a table for this. Given the popularity if the IANA tz system, I can only hope that one day Microsoft will include support for it in their products. Thanks for the links, I will look into them.

Challenge is still unsolved.

[amitchandak]

@h4tt3n , refer if these can help

https://community.powerbi.com/t5/Desktop/Convert-utc-to-local-time-zone-using-Power-Query/td-p/55557...

https://community.powerbi.com/t5/Desktop/Convert-UTC-to-local-time-zone-in-DAX/td-p/43328

[h4tt3n]

@amitchandak Sorry, but these suggestions won't work. It has to be a function that specifically deals with the IANA timezone format Region/City, eg Europe/Paris to GMT or UTC. 

I am still looking for a way to solve this.