Skip to main content
B
brookd
Inspiring
September 16, 2021
Answered

How to Convert a TimeZone name/id from Java to the format recognized by SQL Server / Windows

  • September 16, 2021
  • 1 reply
  • 2547 views

Hey Folks,

 

We store timezone IDs from java.util.TimeZone, and now we want to use them with SQL Server (2019)'s AT TIMEZONE function. The problem is, the Java name for the timezone is not the same as the name SQL server wants. 

 

For example, we store the timezone ID from Java 'Australia/Adelaide'. We can get the full name via java.util.TimeZone.getTimeZone('Australia/Adelaide').getDisplayName(), which returns 'Australian Central Standard Time (South Australia)'. 

 

But.... SQL Server wants 'Australian Central Standard Time' and throws an error when using the string returned from Java.

 

--- this works
select datetime AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'Australian Central Standard' AS datetime_TimeZone

-- this fails
select datetime AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'Australian Central Standard Time (South Australia)' AS datetime_TimeZone

 

Note, our dates are stored in PST and there is no way to easily change that now.  We just need to be able to convert Java's name of the timezone to the one SQL server expects which I believe is based on the window registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones

 

Has anyone had to deal with this or have any idea?

This topic has been closed for replies.
Correct answer brookd

I found a XML file that has all the conversions! Still testing it but so far looks like like it is perfect. Link is here https://raw.githubusercontent.com/unicode-org/cldr/main/common/supplemental/windowsZones.xml 

1 reply

D
Community Expert
Dave WattsCommunity Expert
Community Expert
September 16, 2021

This might be a dumb idea, but can you just manipulate the string you're getting from Java before using it in your SQL? For example, if the part before the parentheses is always a match, just clip off everything starting with the space before the opening parenthesis.

 

Dave Watts, Eidolon LLC

Dave Watts, Eidolon LLC
Charlie Arehart
Community Expert
Charlie ArehartCommunity Expert
Community Expert
September 16, 2021

And if Dave's idea doesn't pan out, I will say that storing timezone strings is just fraught with peril. That's why the recommendation is nearly always to store things in UTC. Heck, even what may be "standard" time one day would be off by an hour half the year (in most timezones), right? But perhaps you are somehow already dealing with that.

 

And I know you said, Brook, that "our dates are stored in PST and there is no way to easily change that now."  I realize that may be your "final answer" (to quote WWTBAM). Just wanted to put this out there. 🙂

/Charlie (troubleshooter, carehart. org)
B
brookdAuthor
Inspiring
September 16, 2021

Aww, Charlie, you are right, and I wish the original designer had used UTC. But alas, all the dates are stored in PST. So we run everthing through time zone conversion code which is slow, and painful. SQL Servers' AT TIMEZONE function is helpful in translating the dates though, and is one of the workarounds we are currently reviewing. 

 

Thanks for the replies guys! You two are veterans!! Allaire day vets! There's no way your profile pics are recent guys, come on this isn't a dating site 😉 jk 

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices