AT TIME ZONE – SQL Server 2016

The other day I was required to find out more about offsets and timezones and I came across a new T-SQL function in SQL Server 2016 AT TIME ZONE.

This function gives us the current offset in the target time zone.

For example, let’s look at GETDATE(), GETUTCDATE() and GETDATE() with AT TIME ZONE.

SELECT CONVERT(NVARCHAR(30), GETDATE(), 126) AS [Current_Time]

SELECT CONVERT(NVARCHAR(30), GETUTCDATE(), 126) AS [Current_UTCTime]

SELECT CONVERT(DATETIME2(3), GETDATE(), 126) AT TIME ZONE 'GMT Standard Time' AS [Current_Time_WithOffset]

Results in the following output;

As you can see the last one show’s the current offset (+01:00) as we’re in Daylight Savings Time (British Summer Time).

The is also table sys.time_zone_info which contains the supported time zones;

Note that this function is classed as nondeterministic as SQL Server relies on an external source, time zones that are stored in the Windows Registry.

This function is also available inthe Azure SQL Database PaaS offering. The complete T-SQL information is available here.

Thanks for reading!