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)
[...] 16, 2010 by pinaldave Earlier I wrote about SQL SERVER – Datetime Function SWITCHOFFSET Example. After reading this blog post, I got another quick reply that if I can explain the usage of [...]
[...] SQL SERVER – Datetime Function SWITCHOFFSET Example [...]
[...] Datetime Datetime Function TODATETIMEOFFSET Example Information Related to DATETIME and DATETIME2 Datetime Function SWITCHOFFSET Example Difference Between DATETIME and DATETIME2 – WITH GETDATE DATE and TIME in SQL Server [...]
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.
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