Menu

SQL Server SWITCHOFFSET() Function

Updated on

SWITCHOFFSET(datetimeoffset_expression, time_zone) returns a datetimeoffset value with the specified offset while preserving the same UTC instant. It requires an input that resolves to datetimeoffset; it does not interpret a plain datetime as a time-zone-aware value. The target is a numeric offset, not a named time zone. See Microsoft’s SWITCHOFFSET reference.

Syntax

SWITCHOFFSET(datetimeoffset_expression, time_zone)

time_zone can be a string such as '+00:00' or an integer number of offset minutes. The returned wall-clock time changes as needed to keep the instant in UTC unchanged.

Display an existing value at a different offset

The input below represents 10:00 at UTC-05:00, which is 15:00 UTC. Switching to +00:00 changes the displayed clock time to 15:00 while preserving that instant:

DECLARE @value datetimeoffset(0) = '2022-03-10T10:00:00-05:00';

SELECT SWITCHOFFSET(@value, '+00:00') AS utc_value;

Result:

2022-03-10 15:00:00 +00:00

SWITCHOFFSET() does not calculate a named zone’s historical or daylight-saving offset. To convert between named time zones using SQL Server’s zone rules, use AT TIME ZONE. To attach a known offset to a local datetime2 value without changing its clock time, use TODATETIMEOFFSET().