Menu

SQL Server AT TIME ZONE

inputdate AT TIME ZONE timezone converts a date/time value to datetimeoffset using a named time zone. It was introduced in SQL Server 2016 (13.x). When the input has no offset, SQL Server interprets it as local time in the named zone and attaches that zone’s offset. When the input is already datetimeoffset, SQL Server converts the same instant to the target zone. See Microsoft’s AT TIME ZONE reference.

Check available time-zone names

SQL Server uses installed Windows time-zone names, not IANA names such as America/Los_Angeles. Check the names available on the server with sys.time_zone_info:

SELECT name, current_utc_offset, is_currently_dst
FROM sys.time_zone_info
WHERE name = 'Pacific Standard Time';

On SQL Server for Linux, the host’s Linux time zone is mapped to a Windows time-zone identifier for T-SQL commands. Use the Windows identifier in AT TIME ZONE even if the Linux host is configured with an IANA name. See Microsoft’s SQL Server on Linux time-zone mapping.

Interpret local time and convert UTC to a named zone

If a datetime2 value is known to be UTC, first attach the UTC zone and then convert to the destination. The example converts noon UTC to Pacific time in July:

DECLARE @utc_time datetime2(0) = '2024-07-01T12:00:00';

SELECT @utc_time AT TIME ZONE 'UTC'
                  AT TIME ZONE 'Pacific Standard Time' AS pacific_time;

The result is 2024-07-01 05:00:00 -07:00. If a datetime2 value is instead local wall time in the destination zone, apply that zone once:

DECLARE @pacific_local datetime2(0) = '2024-07-01T05:00:00';

SELECT @pacific_local AT TIME ZONE 'Pacific Standard Time';

Do not label a UTC value as local time or apply the same zone twice; the input’s meaning determines whether to use one conversion step or two.

Daylight-saving gaps and overlaps

When clocks move forward, some local wall-clock times do not exist. SQL Server moves such a value forward by the size of the DST gap and uses the post-change offset. When clocks move back, a local time can occur twice; SQL Server uses the offset from before the clock change for the ambiguous interval. These rules mean that converting an ambiguous or nonexistent local time can select an interpretation automatically.

AT TIME ZONE is nondeterministic because the underlying time-zone rules are maintained outside SQL Server and can change. If the time zone is known, use the named zone so historical daylight-saving rules are applied. For a fixed numeric offset, see TODATETIMEOFFSET(); for an existing datetimeoffset value that only needs a different offset, see SWITCHOFFSET().