AT TIME ZONE (Transact-SQL)

Converts an inputdate to the corresponding datetimeoffset value in the target time zone. When inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone. If inputdate is provided as a datetimeoffset value, then AT TIME ZONE clause converts it into the target time zone using the time zone conversion rules.

AT TIME ZONE implementation relies on a Windows mechanism to convert datetime values across time zones.

SELECT GETDATE() AT TIME ZONE 'Central European Standard Time';  

SELECT GETDATE() AT TIME ZONE 'Pacific Standard Time';  

SELECT SalesOrderID, OrderDate,
    OrderDate AT TIME ZONE 'Pacific Standard Time' AS OrderDate_TimeZonePST,
    OrderDate AT TIME ZONE 'Central European Standard Time' AS OrderDate_TimeZoneCET
FROM Sales.SalesOrderHeader;

Applies to:

• SQL Server 2016 (13.x) and later 
• Azure SQL Database

Sources:

https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver15