In SQL Server, the SWITCHOFFSET() function can be used to return a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

Below are examples of how this function works.

Syntax

First, here’s how the syntax goes:

SWITCHOFFSET ( DATETIMEOFFSET, time_zone )

Where DATETIMEOFFSET is an expression that can be resolved to a datetimeoffset(n) value, and time_zone is a character string in the format [+|-]TZH:TZM or a signed integer (of minutes) that represents the time zone offset, and is assumed to be daylight-saving aware and adjusted.

The result is returned as a datetimeoffset with the fractional precision of the DATETIMEOFFSET argument.

Example 1

Here’s a basic example of usage:

SELECT SWITCHOFFSET( '2112-01-01', '+08:00' ) AS Result;

Result:

Result
----------------------------------
2112-01-01 08:00:00.0000000 +08:00

Example 2

This example uses a negative value:

SELECT SWITCHOFFSET( '2112-01-01', '-08:00' ) AS Result;

Result:

Result
----------------------------------
2111-12-31 16:00:00.0000000 -08:00

Example 4

In this example we declare a variable and assign a date to it using the datetimeoffset data type. We then apply SWITCHOFFSET() to that date and compare it with the original date.

DECLARE @date datetimeoffset = '2112-01-01 00:00:00.0000000 +04:00';
SELECT 
    @date AS 'Original Date',
    SWITCHOFFSET( @date, '+08:00' ) AS '+08:00';

Result:

Original Date                      +08:00
---------------------------------- ----------------------------------
2112-01-01 00:00:00.0000000 +04:00 2112-01-01 04:00:00.0000000 +08:00

Example 5

Here we do the same thing as the previous example, but we use the SYSDATETIMEOFFSET() function to generate the current date/time and offset.

SELECT 
    SYSDATETIMEOFFSET() AS 'Current Date',
    SWITCHOFFSET( SYSDATETIMEOFFSET(), '+08:00' ) AS '+08:00';

Result:

Current Date                       +08:00
---------------------------------- ----------------------------------
2018-06-05 17:05:36.9415309 +10:00 2018-06-05 15:05:36.9415309 +08:00

Example 6

This example is the same as the previous one, except we add a negative value.

SELECT 
    SYSDATETIMEOFFSET() AS 'Current Date',
    SWITCHOFFSET( SYSDATETIMEOFFSET(), '-08:00' ) AS '-08:00';

Result:

Current Date                       -08:00
---------------------------------- ----------------------------------
2018-06-05 17:12:07.1122856 +10:00 2018-06-04 23:12:07.1122856 -08:00

Example 7

You can also provide the time zone offset as an integer instead of a string:

SELECT SWITCHOFFSET( '2112-01-01', -180 ) AS Result;

Result:

Result
----------------------------------
2111-12-31 21:00:00.0000000 -03:00