SQL Server SWITCHOFFSET Function

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

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 )Copy

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
);Copy

Second, insert a new DATETIMEOFFSET value into the table:

INSERT INTO dbo.switchoffset_demo
VALUES('2019-03-06 9:20:00 +07:00');Copy

Third, query value from the dbo.switchoffset_demo table:

SELECT 
    dtz
FROM 
    dbo.switchoffset_demo;Copy

Here is the output:

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

(1 row affected)Copy

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

SELECT 
    SWITCHOFFSET(dtz, '-08:00') result
FROM 
    dbo.switchoffset_demo;Copy

The output is as follows:

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

(1 row affected)Copy

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