Timezone mapping based SQL Server time zone info

Question

I have following timezone list, which is a drop down in my application, I am facing now issue of daylight saving, I need to attached addition column in my table from sys.time_zone_info so can use AT TIME ZONE function:

-12:00  (UTC-12:00) International Date Line West
-11:00  (UTC-11:00) Coordinated Universal Time-11
-11:00  (UTC-11:00) Samoa
-10:00  (UTC-10:00) Hawaii
-09:00  (UTC-09:00) Alaska
-08:00  (UTC-08:00) Baja California
-08:00  (UTC-08:00) Pacific Time (US & Canada)
-07:00  (UTC-07:00) Arizona
-07:00  (UTC-07:00) Chihuahua, La Paz, Mazatlan
-07:00  (UTC-07:00) Mountain Time (US & Canada)
-06:00  (UTC-06:00) Central America
-06:00  (UTC-06:00) Central Time (US & Canada)
-06:00  (UTC-06:00) Guadalajara, Mexico City, Monterrey
-06:00  (UTC-06:00) Saskatchewan
-05:00  (UTC-05:00) Bogota, Lima, Quito
-05:00  (UTC-05:00) Eastern Time (US & Canada)
-05:00  (UTC-05:00) Indiana (East)
-04:30  (UTC-04:30) Caracas
-04:00  (UTC-04:00) Asuncion
-04:00  (UTC-04:00) Atlantic Time (Canada)
-04:00  (UTC-04:00) Cuiaba
-04:00  (UTC-04:00) Georgetown, La Paz, Manaus, San Juan
-04:00  (UTC-04:00) Santiago
-03:30  (UTC-03:30) Newfoundland
-03:00  (UTC-03:00) Brasilia
-03:00  (UTC-03:00) Buenos Aires
-03:00  (UTC-03:00) Cayenne, Fortaleza
-03:00  (UTC-03:00) Greenland
-03:00  (UTC-03:00) Montevideo
-02:00  (UTC-02:00) Coordinated Universal Time-02
-02:00  (UTC-02:00) Mid-Atlantic
-01:00  (UTC-01:00) Azores
-01:00  (UTC-01:00) Cape Verde Is.
+00:00  (UTC) Casablanca
+00:00  (UTC) Coordinated Universal Time
+00:00  (UTC) Dublin, Edinburgh, Lisbon, London
+00:00  (UTC) Monrovia, Reykjavik
+01:00  (UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna
+01:00  (UTC+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague
+01:00  (UTC+01:00) Brussels, Copenhagen, Madrid, Paris
+01:00  (UTC+01:00) Sarajevo, Skopje, Warsaw, Zagreb
+01:00  (UTC+01:00) West Central Africa
+01:00  (UTC+01:00) Windhoek
+02:00  (UTC+02:00) Amman
+02:00  (UTC+02:00) Athens, Bucharest, Istanbul
+02:00  (UTC+02:00) Beirut
+02:00  (UTC+02:00) Cairo
+02:00  (UTC+02:00) Damascus
+02:00  (UTC+02:00) Harare
+02:00  (UTC+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius
+02:00  (UTC+02:00) Jerusalem
+02:00  (UTC+02:00) Minsk
+03:00  (UTC+03:00) Baghdad
+03:00  (UTC+03:00) Kuwait, Riyadh
+03:00  (UTC+03:00) Moscow, St. Petersburg, Volgograd
+03:00  (UTC+03:00) Nairobi
+03:30  (UTC+03:30) Tehran
+04:00  (UTC+04:00) Abu Dhabi, Muscat
+04:00  (UTC+04:00) Baku
+04:00  (UTC+04:00) Port Louis
+04:00  (UTC+04:00) Tbilisi
+04:00  (UTC+04:00) Yerevan
+04:30  (UTC+04:30) Kabul
+05:00  (UTC+05:00) Ekaterinburg
+05:00  (UTC+05:00) Islamabad, Karachi
+05:00  (UTC+05:00) Tashkent
+05:30  (UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi
+05:30  (UTC+05:30) Sri Jayawardenepura
+05:45  (UTC+05:45) Kathmandu
+06:00  (UTC+06:00) Astana
+06:00  (UTC+06:00) Dhaka
+06:00  (UTC+06:00) Novosibirsk
+06:30  (UTC+06:30) Yangon (Rangoon)
+07:00  (UTC+07:00) Bangkok, Hanoi, Jakarta
+07:00  (UTC+07:00) Krasnoyarsk
+08:00  (UTC+08:00) Beijing, Chongqing, Hong Kong, Urumqi
+08:00  (UTC+08:00) Irkutsk
+08:00  (UTC+08:00) Kuala Lumpur, Singapore
+08:00  (UTC+08:00) Perth
+08:00  (UTC+08:00) Taipei
+08:00  (UTC+08:00) Ulaanbaatar
+09:00  (UTC+09:00) Osaka, Sapporo, Tokyo
+09:00  (UTC+09:00) Seoul
+09:00  (UTC+09:00) Yakutsk
+09:30  (UTC+09:30) Adelaide
+09:30  (UTC+09:30) Darwin
+10:00  (UTC+10:00) Brisbane
+10:00  (UTC+10:00) Canberra, Melbourne, Sydney
+10:00  (UTC+10:00) Guam, Port Moresby
+10:00  (UTC+10:00) Hobart
+10:00  (UTC+10:00) Vladivostok
+11:00  (UTC+11:00) Magadan
+11:00  (UTC+11:00) Solomon Is., New Caledonia
+12:00  (UTC+12:00) Auckland, Wellington
+12:00  (UTC+12:00) Coordinated Universal Time+12
+12:00  (UTC+12:00) Fiji
+12:00  (UTC+12:00) Petropavlovsk-Kamchatsky - Old
+13:00  (UTC+13:00) Nuku,alofa

I need to map with the following SQL Server time zone, how can I map this with with my custom time zone list, because I don't know exactly which timezone is correct for my custom timezone.

SELECT * 
FROM sys.time_zone_info

is_currently_dst    current_utc_offset  name  
---------------------------------------------------
0   -12:00  Dateline Standard Time
0   -11:00  UTC-11
0   -10:00  Hawaiian Standard Time
0   -09:30  Marquesas Standard Time
0   -09:00  UTC-09
0   -08:00  UTC-08
0   -06:00  Canada Central Standard Time
0   -05:00  SA Pacific Standard Time
0   -05:00  Eastern Standard Time (Mexico)
0   -07:00  US Mountain Standard Time
0   -06:00  Central America Standard Time
0   -06:00  Easter Island Standard Time
0   -04:00  Paraguay Standard Time
0   -04:00  Venezuela Standard Time
0   -04:00  Central Brazilian Standard Time
0   -04:00  SA Western Standard Time
0   -04:00  Pacific SA Standard Time
0   -03:00  Tocantins Standard Time
0   -03:00  E. South America Standard Time
0   -03:00  SA Eastern Standard Time
0   -03:00  Argentina Standard Time
0   -03:00  Bahia Standard Time
0   -02:00  UTC-02
0   -01:00  Cape Verde Standard Time
0   +00:00  UTC
0   +00:00  Greenwich Standard Time
0   -03:00  Montevideo Standard Time
0   -03:00  Magallanes Standard Time
0   +01:00  Sao Tome Standard Time
0   +01:00  W. Central Africa Standard Time
0   +02:00  Egypt Standard Time
0   +02:00  Kaliningrad Standard Time
0   +02:00  Sudan Standard Time
0   +02:00  Libya Standard Time
0   +02:00  Namibia Standard Time
0   +03:00  Arabic Standard Time
0   +03:00  Turkey Standard Time
0   +03:00  Arab Standard Time
0   +03:00  Belarus Standard Time
0   +03:00  Russian Standard Time
0   +03:00  E. Africa Standard Time
0   +04:00  Arabian Standard Time
0   +04:00  Astrakhan Standard Time
0   +04:00  Azerbaijan Standard Time
0   +04:00  Russia Time Zone 3
0   +04:00  Mauritius Standard Time
0   +04:00  Saratov Standard Time
0   +04:00  Georgian Standard Time
0   +04:00  Caucasus Standard Time
0   +04:30  Afghanistan Standard Time
0   +05:00  West Asia Standard Time
0   +05:00  Ekaterinburg Standard Time
0   +05:00  Pakistan Standard Time
0   +05:30  India Standard Time
0   +05:30  Sri Lanka Standard Time
0   +05:45  Nepal Standard Time
0   +06:00  Central Asia Standard Time
0   +06:00  Bangladesh Standard Time
0   +06:00  Omsk Standard Time
0   +06:30  Myanmar Standard Time
0   +07:00  SE Asia Standard Time
0   +07:00  Altai Standard Time
0   +07:00  W. Mongolia Standard Time
0   +07:00  North Asia Standard Time
0   +07:00  N. Central Asia Standard Time
0   +07:00  Tomsk Standard Time
0   +08:00  China Standard Time
0   +08:00  North Asia East Standard Time
0   +08:00  Singapore Standard Time
0   +08:00  W. Australia Standard Time
0   +08:00  Taipei Standard Time
0   +08:00  Ulaanbaatar Standard Time
0   +08:30  North Korea Standard Time
0   +08:45  Aus Central W. Standard Time
0   +09:00  Transbaikal Standard Time
0   +09:00  Tokyo Standard Time
0   +09:00  Korea Standard Time
0   +09:00  Yakutsk Standard Time
0   +09:30  Cen. Australia Standard Time
0   +09:30  AUS Central Standard Time
0   +10:00  E. Australia Standard Time
0   +10:00  AUS Eastern Standard Time
0   +10:00  West Pacific Standard Time
0   +10:00  Tasmania Standard Time
0   +10:00  Vladivostok Standard Time
0   +10:30  Lord Howe Standard Time
0   +11:00  Bougainville Standard Time
0   +11:00  Russia Time Zone 10
0   +11:00  Magadan Standard Time
0   +11:00  Norfolk Standard Time
0   +11:00  Sakhalin Standard Time
0   +11:00  Central Pacific Standard Time
0   +12:00  Russia Time Zone 11
0   +12:00  New Zealand Standard Time
0   +12:00  UTC+12
0   +12:00  Fiji Standard Time
0   +12:45  Chatham Islands Standard Time
0   +13:00  UTC+13
0   +13:00  Tonga Standard Time
0   +13:00  Samoa Standard Time
0   +14:00  Line Islands Standard Time
0   +02:00  South Africa Standard Time
1   +03:00  FLE Standard Time
1   +03:00  Israel Standard Time
1   +13:00  Kamchatka Standard Time
1   +04:30  Iran Standard Time
1   +03:00  E. Europe Standard Time
1   +03:00  Syria Standard Time
1   +03:00  West Bank Standard Time
1   +03:00  Jordan Standard Time
1   +03:00  GTB Standard Time
1   +03:00  Middle East Standard Time
1   +02:00  Central European Standard Time
1   -02:00  Saint Pierre Standard Time
1   +02:00  W. Europe Standard Time
1   +02:00  Central Europe Standard Time
1   +02:00  Romance Standard Time
1   +01:00  Morocco Standard Time
1   +01:00  GMT Standard Time
1   -01:00  Mid-Atlantic Standard Time
1   +00:00  Azores Standard Time
1   -02:00  Greenland Standard Time
1   -02:30  Newfoundland Standard Time
1   -03:00  Atlantic Standard Time
1   -05:00  Central Standard Time (Mexico)
1   -05:00  Central Standard Time
1   -06:00  Mountain Standard Time (Mexico)
1   -06:00  Mountain Standard Time
1   -04:00  Eastern Standard Time
1   -04:00  Haiti Standard Time
1   -04:00  Cuba Standard Time
1   -04:00  US Eastern Standard Time
1   -04:00  Turks And Caicos Standard Time
1   -07:00  Pacific Standard Time
1   -07:00  Pacific Standard Time (Mexico)
1   -08:00  Alaskan Standard Time
1   -09:00  Aleutian Standard Time

The items in the first list appear to be *display names*, while the ones in SQL server are the corresponding IDs. Is this a .NET Application? If so, both are on the `TimeZoneInfo` object. You tagged the question with `moment-timezone`, which is JavaScript and doesn't use these IDs, so I'm not sure what exactly you are doing in your application. — Matt Johnson-Pint, Jul 16 '18 at 16:57
You say in your question, "...which is a drop down in my application", so clearly you are not *just* using SQL server. What language/platform is that application? What code generates that list? — Matt Johnson-Pint, Jul 16 '18 at 23:03
Note, it is not just a question of one list vs another. Display names vary between OS language (English vs Spanish, etc.) whereas IDs are consistent. Also display names have changed over time, even for the same entry and the same language. — Matt Johnson-Pint, Jul 16 '18 at 23:05

Timezone mapping based SQL Server time zone info

0 Answers0