Change the Time Zone Offset on a datetimeoffset Value in SQL Server (T-SQL)

You can use the SWITCHOFFSET() function in SQL Server to change the time zone offset on a datetimeoffset value.

The function accepts two arguments; a datetimeoffset(n) value (or an expression that can be resolved to a datetimeoffset(n) value), and the new time zone.

Example

Here’s an example to demonstrate.

DECLARE @dto datetimeoffset = '2020-12-20 17:33:59.8900000 +02:00';
SELECT
  @dto AS [Original],
  SWITCHOFFSET(@dto, '+04:00') AS [Modified];

Result (using vertical output):

Original | 2020-12-20 17:33:59.8900000 +02:00
Modified | 2020-12-20 19:33:59.8900000 +04:00

Note that the time zone offset is specified as a string. This is because I provided it in hours.

When specified in hours, the time zone offset must use the format [+|-]TZH:TZM and be specified as a string (enclosed in single quotes).

Use Minutes as the Time Zone Offset

You can alternatively specify the time zone offset in minutes. When doing this, you must specify it as an integer.

DECLARE @dto datetimeoffset = '2020-12-20 17:33:59.8900000 +02:00';
SELECT
  @dto AS [Original],
  SWITCHOFFSET(@dto, 240) AS [Modified];

Result (using vertical output):

Original | 2020-12-20 17:33:59.8900000 +02:00
Modified | 2020-12-20 19:33:59.8900000 +04:00

Database Example

Here’s an example of using SWITCHOFFSET() on the value from a column in a database table.

CREATE TABLE Test 
  (
      Col1 datetimeoffset
  ); 
INSERT INTO Test   
VALUES ('1967-12-20 17:33:59.8900000 -07:00'); 
SELECT 
  Col1,
  SWITCHOFFSET(Col1, '-08:00') AS Modified
FROM Test;

Result (using vertical output):

Col1     | 1967-12-20 17:33:59.8900000 -07:00
Modified | 1967-12-20 16:33:59.8900000 -08:00

Performance When using a GETDATE() Value

Microsoft advises that using SWITCHOFFSET() with the GETDATE() function can cause the query to run slowly, due to the query optimiser being unable to obtain accurate cardinality estimates for the datetime value.

To resolve this problem, use the OPTION (RECOMPILE) query hint. This forces the query optimiser to recompile a query plan the next time the same query is executed.

Don’t Know the Time Zone Offset?

If you don’t know the time zone offset to use, here’s how to get a list of supported time zones in SQL Server.

One thing you need to be mindful of is daylight savings. Many countries/regions have their own specific rules for observing daylight savings (and some don’t observe it at all). This can cause all sorts of problems when you’re trying to work out whether or not to factor daylight savings into your time zone offsets.

Fortunately, SQL Server came up with a way to deal with this. SQL Server 2016 introduced the AT TIME ZONE clause. This clause allows you to specify the name of a time zone, rather than the time zone offset itself. Therefore, you can simply use ‘US Mountain Standard Time’ or ‘India Standard Time’ or whatever time zone is applicable.

See Convert a Date to Another Time Zone for examples on how to do this.