Azure SQL Convert "Time zone display name" to "Time zone ID"

Question

I am working with the latest version of Azure SQL. I have a series of Time zone display name and want to convert those into Time zone ID. I know SQL server contains both the display name and the id. sys.time_zone_info has all the Time zone ID, but commands like CURRENT_TIMEZONE () output Time zone display name. What I am hoping for is a simple function that takes a string Time zone display name and returns the Time zone ID that name maps to, but I can't find any reference to that. And I can't find a table that actually stores the mapping.

Reference - Time zones in Azure SQL Managed Instance

score 2 · Accepted Answer · answered Feb 17 '21 at 18:21

2

In sys.time_zone_info, the name is the ID. Not the greatest for normalization purposes.

If you're using this to have your own internal time zone list for your application, then I think Joseph Xu has the correct answer.

If you're using this for queries against Azure SQL expecting time zone localization, bear in mind that, according to Azure SQL documentation, the database operates in UTC, and CURRENT_TIMEZONE_ID() will return UTC always.

answered Feb 17 '21 at 18:21

WaitingForGuacamole

3,744
1
8
22

2

Nice walkthrough @WaitingForGuacamole, +10 from me. – Joseph Xu Feb 25 '21 at 01:42
In the end, I had to create a table to do the transition as expected. I was hoping someone would know some cheeky function that would allow me to get from like "(UTC-05:00) Chetumal", to "Eastern Standard Time (Mexico)" where the latter is the id used in `sys.time_zone_info` – Jeff Apr 13 '21 at 21:43

score 1 · Answer 2 · answered Feb 09 '21 at 03:18

1

I think we can create a table to store the infos with two columns Time zone ID and Time zone display name. Then create a stored procedure to query that, input Time zone display name return Time zone ID.

answered Feb 09 '21 at 03:18

Joseph Xu

5,607
2
5
15

score 0 · Answer 3 · answered Mar 09 '22 at 16:06

As I know, SQL Azure does not provide tables to convert from Time Zone ID to Time Zone display name. I think there is not any table with that information because the sys.time_zone_info system view obtains the information from internal functions instead of other tables you can check.

You may use some tables and functions from the T-SQL Toolbox or you may create your own tables.

Using a custom table

You may obtain (and/or modify) the code to create the tables from the TSqlToolbox. This code creates a table DateTimeUtil.Timezone with the desired information.

I made an example you may try in db<>fiddle

SELECT identifier 
  FROM [TimeZone]
 WHERE [TimeZone].[DisplayName] = '(UTC-08: 00) Baja California'

| Identifier                       |
|----------------------------------|
| Pacific Standard Time (Mexico)   |

Azure SQL Convert "Time zone display name" to "Time zone ID"

3 Answers3

Using a custom table