SQL Server SWITCHOFFSET Function

Summary: in this tutorial, you will learn how to use the SWITCHOFFSET() function to switch a DATETIMEOFFSET value to a new time zone offset.

Introduction to SQL Server SWITCHOFFSET() function

The SWITCHOFFSET() function returns a DATETIMEOFFSET changed from the stored time zone offset to a new time zone offset.

The following illustrates the syntax of SWITCHOFFSET() function:

SWITCHOFFSET( expression, time_zone )   
Code language: SQL (Structured Query Language) (sql)

The SWITCHOFFSET() function accepts two arguments:

• expression is an expression that can resolve to a DATETIMEOFFSET value.
• time_zone can be a character string in the format {+|-}TZH:TZM, or a signed integer of minutes. For example, time_zone can be +08:00, -07:00, or 120.

The SWITCHOFFSET() function is very handy to update values in a DATETIMEOFFSET column.

SQL Server SWITCHOFFSET() function example

Let’s take the example of using the SQL Server SWITCHOFFSET() function.

First, create a new table that has a DATETIMEOFFSET column:

CREATE TABLE dbo.switchoffset_demo(
    dtz DATETIMEOFFSET
); 
Code language: SQL (Structured Query Language) (sql)

Second, insert a new DATETIMEOFFSET value into the table:

INSERT INTO dbo.switchoffset_demo
VALUES('2019-03-06 9:20:00 +07:00'); 
Code language: SQL (Structured Query Language) (sql)

Third, query value from the dbo.switchoffset_demo table:

SELECT 
    dtz
FROM 
    dbo.switchoffset_demo;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

dtz
----------------------------------
2019-03-06 09:20:00.0000000 +07:00

(1 row affected)Code language: CSS (css)

Fourth, use the SWITCHOFFSET() function to switch the time zone to -08:00:

SELECT 
    SWITCHOFFSET(dtz, '-08:00') result
FROM 
    dbo.switchoffset_demo; 
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

result
----------------------------------
2019-03-05 18:20:00.0000000 -08:00

(1 row affected)Code language: CSS (css)

In this tutorial, you have learned how to use the SWITCHOFFSET() function to switch a time zone for a DATETIMEOFFSET value.