SQL SERVER – Datetime Function SWITCHOFFSET Example

I was recently asked if I know how SWITCHOFFSET works. This feature only works in SQL Server 2008.

Here is quick definition of the same from BOL: Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

What essentially it does is that changes the current offset of the time to any other offset which we defined. Let us see the example of the same.

SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-04:00') 'GetCurrentOffSet-4';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-02:00') 'GetCurrentOffSet-2';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00') 'GetCurrentOffSet+0';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+02:00') 'GetCurrentOffSet+2';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+04:00') 'GetCurrentOffSet+4';

Now let us check the resultset of the same.

SQL SERVER - Datetime Function SWITCHOFFSET Example switchoffset

The example above clearly shows that Switch offset does not only change the offset; it also alters the current time. If you look at GetcurrentOffset, it is +5.30; but, you will notice that GetCurrentOffset-2 does not only change the offset to -2. It also changes the time with the appropriate time at Timezone -2.

I suggest that you run the code in SSMS Query Window and observe the code behavior.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQLAuthority News – Two SQL Sessions at SQL Data Camp at Chennai – July 17, 2010
Next Post
SQL SERVER – Datetime Function TODATETIMEOFFSET Example

Related Posts

4 Comments. Leave new

  • Hi,
    Is there anyway to change the GIVEN datetime into UTC in SQL? It should also consider day light saving. C# has built in command whereas SQL not. Please suggest.

    Reply
  • John Lyndon Philip Planas
    April 5, 2013 4:36 pm

    Hi,

    We are having problem in using the switchoffset built -in function in SQL SERVER 2008 R2. The code we have is working fine in SQL SERVER 2012. Do you know any of relative CU or SP’s of SQL Server 2008 R2 that includes the fix for this?

    The error is : “The timezone provided to builtin function switchoffset is invalid.; 4200.”

    Hope to hear from you. Thanks!

    -John Philip

    Reply
  • Hi Pinal Dave. Thanks for your great site. I always end up there with my sql questions, and you answer them all pretty much, so really, thank you.

    As for ‘does not only change the offset; it also alters the current time.’, this is kind of right.

    The way a DateTimeOffset works is that is shows: LOCALDateTime +-Offset that time is from UTC.

    So yes, it does ‘change the date time’ but only to show the new ‘local’ date time. You can still always get the exact same date time back by using switch offset again. So the underlying date time never changes.

    Reply
  • myprogrammingexp
    March 26, 2014 8:44 pm

    How will this work, if I have saved the time values without the timezone?

    Reply

Leave a Reply

Menu