How to Convert UTC to Local Time Zone in SQL Server
SQL Server does not store time zone data when storing timestamps. It uses the host server time as the basis for generating the output of getdate().
To convert a UTC timestamp to a local time zone, you can use the following:
-- all SQL Server versions
declare @utc_date datetime = getdate()
select @utc_date as utc_time_zone,
dateadd(hh, datediff(hh, getutcdate(), getdate()), @utc_date) as local_time_zone
--SQL Server 2016 and later
declare @utc_date datetime = getdate()
select @utc_date as utc_time_zone,
getdate() at time zone 'US Eastern Standard Time' as time_zone_est;To see the list of time zones SQL Server supports:
select * from sys.time_zone_info;name | current_utc_offset | is_currently_dst
--------------------------------+--------------------+------------------
Dateline Standard Time | -12:00 | false
UTC-11 | -11:00 | false
Aleutian Standard Time | -10:00 | false
Hawaiian Standard Time | -10:00 | false
Marquesas Standard Time | -09:30 | false
Alaskan Standard Time | -09:00 | false
UTC-09 | -09:00 | false
Pacific Standard Time (Mexico) | -08:00 | false
UTC-08 | -08:00 | false
Pacific Standard Time | -08:00 | false
US Mountain Standard Time | -07:00 | false
Mountain Standard Time (Mexico) | -07:00 | false
Mountain Standard Time | -07:00 | false
Central America Standard Time | -06:00 | false
Central Standard Time | -06:00 | false
Easter Island Standard Time | -05:00 | true
Central Standard Time (Mexico) | -06:00 | false
Canada Central Standard Time | -06:00 | false
SA Pacific Standard Time | -05:00 | false
Eastern Standard Time (Mexico) | -05:00 | false
Eastern Standard Time | -05:00 | false
Haiti Standard Time | -05:00 | false
Cuba Standard Time | -05:00 | false
US Eastern Standard Time | -05:00 | false
Turks And Caicos Standard Time | -05:00 | false
Paraguay Standard Time | -03:00 | true
Atlantic Standard Time | -04:00 | false
Venezuela Standard Time | -04:00 | false
Central Brazilian Standard Time | -03:00 | true
SA Western Standard Time | -04:00 | false
Pacific SA Standard Time | -03:00 | true
Newfoundland Standard Time | -03:30 | false
Tocantins Standard Time | -03:00 | false
E. South America Standard Time | -02:00 | true
SA Eastern Standard Time | -03:00 | false
Argentina Standard Time | -03:00 | false
Greenland Standard Time | -03:00 | false
Montevideo Standard Time | -03:00 | false
Magallanes Standard Time | -03:00 | false
Saint Pierre Standard Time | -03:00 | false
Bahia Standard Time | -03:00 | false
UTC-02 | -02:00 | false
Mid-Atlantic Standard Time | -02:00 | false
Azores Standard Time | -01:00 | false
Cape Verde Standard Time | -01:00 | false
UTC | +00:00 | false
GMT Standard Time | +00:00 | false
Greenwich Standard Time | +00:00 | false
W. Europe Standard Time | +01:00 | false
Central Europe Standard Time | +01:00 | false
Romance Standard Time | +01:00 | false
Morocco Standard Time | +01:00 | false
Sao Tome Standard Time | +01:00 | false
Central European Standard Time | +01:00 | false
W. Central Africa Standard Time | +01:00 | false
Jordan Standard Time | +02:00 | false
GTB Standard Time | +02:00 | false
Middle East Standard Time | +02:00 | false
Egypt Standard Time | +02:00 | false
E. Europe Standard Time | +02:00 | false
Syria Standard Time | +02:00 | false
West Bank Standard Time | +02:00 | false
South Africa Standard Time | +02:00 | false
FLE Standard Time | +02:00 | false
Israel Standard Time | +02:00 | false
Kaliningrad Standard Time | +02:00 | false
Sudan Standard Time | +02:00 | false
Libya Standard Time | +02:00 | false
Namibia Standard Time | +02:00 | false
Arabic Standard Time | +03:00 | false
Turkey Standard Time | +03:00 | false
Arab Standard Time | +03:00 | false
Belarus Standard Time | +03:00 | false
Russian Standard Time | +03:00 | false
E. Africa Standard Time | +03:00 | false
Iran Standard Time | +03:30 | false
Arabian Standard Time | +04:00 | false
Astrakhan Standard Time | +04:00 | false
Azerbaijan Standard Time | +04:00 | false
Russia Time Zone 3 | +04:00 | false
Mauritius Standard Time | +04:00 | false
Saratov Standard Time | +04:00 | false
Georgian Standard Time | +04:00 | false
Volgograd Standard Time | +04:00 | false
Caucasus Standard Time | +04:00 | false
Afghanistan Standard Time | +04:30 | false
West Asia Standard Time | +05:00 | false
Ekaterinburg Standard Time | +05:00 | false
Pakistan Standard Time | +05:00 | false
India Standard Time | +05:30 | false
Sri Lanka Standard Time | +05:30 | false
Nepal Standard Time | +05:45 | false
Central Asia Standard Time | +06:00 | false
Bangladesh Standard Time | +06:00 | false
Omsk Standard Time | +06:00 | false
Myanmar Standard Time | +06:30 | false
SE Asia Standard Time | +07:00 | false
Altai Standard Time | +07:00 | false
W. Mongolia Standard Time | +07:00 | false
North Asia Standard Time | +07:00 | false
N. Central Asia Standard Time | +07:00 | false
Tomsk Standard Time | +07:00 | false
China Standard Time | +08:00 | false
North Asia East Standard Time | +08:00 | false
Singapore Standard Time | +08:00 | false
W. Australia Standard Time | +08:00 | false
Taipei Standard Time | +08:00 | false
Ulaanbaatar Standard Time | +08:00 | false
Aus Central W. Standard Time | +08:45 | false
Transbaikal Standard Time | +09:00 | false
Tokyo Standard Time | +09:00 | false
North Korea Standard Time | +09:00 | false
Korea Standard Time | +09:00 | false
Yakutsk Standard Time | +09:00 | false
Cen. Australia Standard Time | +10:30 | true
AUS Central Standard Time | +09:30 | false
E. Australia Standard Time | +10:00 | false
AUS Eastern Standard Time | +11:00 | true
West Pacific Standard Time | +10:00 | false
Tasmania Standard Time | +11:00 | true
Vladivostok Standard Time | +10:00 | false
Lord Howe Standard Time | +11:00 | true
Bougainville Standard Time | +11:00 | false
Russia Time Zone 10 | +11:00 | false
Magadan Standard Time | +11:00 | false
Norfolk Standard Time | +11:00 | false
Sakhalin Standard Time | +11:00 | false
Central Pacific Standard Time | +11:00 | false
Russia Time Zone 11 | +12:00 | false
New Zealand Standard Time | +13:00 | true
UTC+12 | +12:00 | false
Fiji Standard Time | +12:00 | false
Kamchatka Standard Time | +12:00 | false
Chatham Islands Standard Time | +13:45 | true
UTC+13 | +13:00 | false
Tonga Standard Time | +13:00 | false
Samoa Standard Time | +14:00 | true
Line Islands Standard Time | +14:00 | falsePrevious
How to Round Timestamps
