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.

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 (http://blog.SQLAuthority.com)

About these ads

7 thoughts on “SQL SERVER – Datetime Function SWITCHOFFSET Example

  1. Pingback: SQL SERVER – Datetime Function TODATETIMEOFFSET Example Journey to SQL Authority with Pinal Dave

  2. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts Journey to SQL Authority with Pinal Dave

  3. Pingback: SQL SERVER – Lots of Date Functions – Find Right One to Use – Quiz – Puzzle – 27 of 31 « SQL Server Journey with SQL Authority

  4. 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.

    Like

  5. 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

    Like

  6. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s