AT TIME ZONE in Sql Server 2016

AT TIME ZONE in Sql Server 2016

It converts input date time to the corresponding datetimeoffset value in the target time zone.

Syntax:  input_date  AT TIME ZONE timezone

Return Types: Returns the data type of datetimeoffset

Return Value: The datetimeoffset value in the target time zone.

Example:-

--Pacific Standard Time

SELECT CONVERT(DATETIME, GETDATE()) AT TIME ZONE

 N'Pacific Standard Time' AS TimeZonePST

Result:  TimeZonePST - 2017-07-20 12:59:29.607 -07:00

--Central European Standard Time

 SELECT CONVERT(DATETIME2, GETDATE()) AT TIME ZONE

 N'Central European Standard Time' AS TimeZoneCET 

Result:  TimeZoneCET - 2017-07-20 13:02:18.2633333 +02:00

--Time zone date diffrence

SELECT CONVERT(DATETIME, GETDATE()) AT TIME ZONE 'Pacific Standard Time' AS TimeZonePST, 

    CONVERT(DATETIME, GETDATE()) AT TIME ZONE 'Pacific Standard Time'  

    AT TIME ZONE 'Central European Standard Time' AS TimeZoneCET 

Result:  

TimeZonePST                                                    TimeZoneCET

2017-07-20 13:02:56.227 -07:00              2017-07-20 22:02:56.227 +02:00