New SQL 2016 function : AT TIME ZONE

   

     Timezones can be really tricky, SQL Server 2016 brings a new function to help us when we need to  convert/display a date in different timezone.

AT TIME ZONE

Syntax  :  inputdate AT TIME ZONE timezone

inputdate can be smalldatetime, datetime, datetime2 or datetimeoffset

timezone is the name of the destination time zone. SQL Server gets help from Windows Registry for timezone. All supported timezones are stored in the following registry hive : KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones

Also you can find the list of installed time zones in sys.time_zone_info (Transact-SQL)  table

Let's try to display the current datetime in different timezones.