The AT TIME ZONE expression can be used to represent time in a given time zone. It converts an input date to the corresponding datetimeoffset value in the target time zone. It has the following two arguments:

• inputdate: This is an expression of the following date data types: smalldatetime, datetime, datetime2, and datetimeoffset.
• timezone: This is the name of the target time zone. The allowed zone names are listed in the sys.time_zone_info catalog view.

The return type of the expression is datetimeoffset in the target time zone.

Use the following code to display local UTC time, and the local time in New York and Vienna:

SELECT  
  CONVERT(DATETIME, SYSDATETIMEOFFSET()) AS UTCTime, 
  CONVERT(DATETIME, SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time') AS NewYork_Local, 
  CONVERT(DATETIME, SYSDATETIMEOFFSET() AT TIME ZONE 'Central European Standard Time') AS Vienna_Local; 

This query generates the following result:

As mentioned earlier, the values supported for time zones can be found in a new system catalog, sys.time_zone_info. This is exactly the same list as in the registry KEY_LOCAL_MACHINESOFTWAREMicrosoftWindows NTCurrentVersionTime Zones.

The target time zone does not need to be a literal; it can be wrapped in a variable and parameterized. The following code displays the time in four different time zones:

SELECT name, CONVERT(DATETIME, SYSDATETIMEOFFSET() AT TIME ZONE name) AS local_time  
FROM sys.time_zone_info 
WHERE name IN (SELECT value FROM STRING_SPLIT('UTC,Eastern Standard Time,Central European Standard Time,Russian Standard Time',',')); 

Note that another new function, STRING_SPLIT, is used in this query. The result of the previous query is as follows:

name                                  local_time
---------------------------------     -----------------------
Eastern Standard Time                 2018-02-04 11:27:50.193
UTC                                   2018-02-04 15:27:50.193
Central European Standard Time        2018-02-04 17:27:50.193
Russian Standard Time                 2018-02-04 18:27:50.193

By using AT TIME ZONE, you can convert a simple DATETIME value without a time zone offset to any time zone by using its name. What time is it in Seattle, when a clock in Vienna shows 22:33 today, on Super Bowl Sunday (4th February 2018)? Here is the answer:

SELECT CAST('20180204 22:33' AS DATETIME)  
AT TIME ZONE 'Central European Standard Time'  
AT TIME ZONE 'Pacific Standard Time' AS Seattle_Time; 

In Seattle, it is half past two in the afternoon:

Seattle_Time
---------------------------------
2018-02-04 14:33:00.000 -07:00

Note that you must convert the string value to datetime. Usually, a string literal formatted as YYYMMDD HH:ss is interpreted as a valid datetime value, but in this case, you need to cast it explicitly to the datetime data type.