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

Answer 1

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.

Answer 2

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)   |

Answer 3

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.