MS SQL AT TIME ZONE was introduced in MS SQL version 2016 and the main purpose is to convert an input date to the corresponding datetimeoffset value in the target time zone.

Syntax:

inputdate AT TIME ZONE timezone 

• inputdate - datetime value (smalldatetime, datetime, datetime2, or datetimeoffset)
• timezone - Name of the destination time zone

The expression returns the datetimeoffset value in the target time zone.

SQL Server relies on time zones that are stored in the Windows Registry. All time zones installed on the computer are stored in the following registry entry:

KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones

Example:

A list of installed time zones is also exposed through the sys.time_zone_info view.

Example:

SELECT 
	* 
FROM 
	sys.time_zone_info;

You can get the current time zone of the SQL instance by looking at the registry entry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\ TimeZoneKeyName

Or by using dbo.xp_regread system stored procedure (if you have sysadmin rights).

Example:

USE master;
DECLARE @SystemTimeZone NVARCHAR(256);
EXEC dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', N'TimeZoneKeyName', @SystemTimeZone OUTPUT;
SELECT @SystemTimeZone AS SystemTimeZone;

Since I am from Croatia and Croatia uses "Central European Standard Time" time zone, let's get more info about this time zone only.

Example:

SELECT 
	* 
FROM 
	sys.time_zone_info 
WHERE 
	name = 'Central European Standard Time';

We can see that the current UTC offset is +02:00 hours. Also, is_currently_dst means that daylight saving is currently applied.

Now, let's use the SWITCHOFFSET() function to convert the current Croatian date/time to UTC date/time.

Example:

DECLARE @CurrentCroatianTime DATETIME = GETDATE();
DECLARE @UTCOffset DATETIMEOFFSET = SWITCHOFFSET(@CurrentCroatianTime AT TIME ZONE 'Central European Standard Time', '+00:00');
SELECT 
	@CurrentCroatianTime AS "Current local date/time"
	, @UTCOffset AS "UTC offset"
	, CAST(@UTCOffset AS DATETIME) AS "UTC Date/Time";

We can use SYSUTCDATETIME() function to get the system UTC date/time.

Example:

SELECT 
	SYSUTCDATETIME() AS "System UTC date/time"
	, GETDATE() AS "Current local date/time";

And let's use SWITCHOFFSET() function to get the current date/time in the US Eastern Standard Time time zone.

Example:

DECLARE @LocalOffset NVARCHAR(256) = DATENAME(TZ, SYSUTCDATETIME() AT TIME ZONE 'Central European Standard Time')
DECLARE @CSTOffset NVARCHAR(256) = DATENAME(TZ, SYSUTCDATETIME() AT TIME ZONE 'Central Standard Time')
SELECT 
	GETDATE() AS "Local date/time"
	, @LocalOffset AS "Local offset (CEST)"
	, @CSTOffset AS "US Eastern Standard time offset"
	, CAST(SWITCHOFFSET(SYSUTCDATETIME(), @CSTOffset) AS DATETIME) AS "US Eastern Standard Time";

To conclude, let's get date time values for all time zones in the system.

Example:

SELECT 
	SYSUTCDATETIME() AT TIME ZONE name AS "Time zone date/time offset"
	, CAST(SWITCHOFFSET(SYSUTCDATETIME(), current_utc_offset) AS DATETIME) AS "Time zone date/time"
	, name AS "Time zone name"
	, is_currently_dst AS "Is currently daylight saving"
FROM
	sys.time_zone_info;

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (1.95 kb)