467,915 Members | 1,258 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

Error using built in function SwitchOffset


I am trying to run a query using the SwitchOffset built in function to modify the timezone on a DatetimeOffset field.

I am receiving the following error: "The timezone provided to builtin function switchoffset is invalid."

Here is the query:
Select SWITCHOFFSET(LastFailedOn, Cast((GMTOFFSET * 60) as int)
from MyTable

Note: LastFailedOn is of DatetimeOffset type and GMTOFFSET is of type float.

Also, the range of GMTOFFSET is between -10 and 11 with no NULLs.

Finally, when I replace the time_zone attribute with an integer (ex: -300), the query works no problem. I have tested the time_zone calculation on my data and all values returned are valid signed integers within the valid GMTOFFSET range.


Jan 23 '09 #1
  • viewed: 5809
2 Replies
Expert 2GB
The time_zone variable should either be a string or an integer, not float.

Read more here

Happy coding!

-- CK
Jan 24 '09 #2
To address the time_zone data type comment, I am casting our result to an integer, so there is no problem with the value being a float.

With further testing, I have discovered that by adding an ORDER BY clause to the end of the query, that no error occurs. Also, If I select the one row that has data and one row that does not, it works fine. So does selecting a group of specific rows.

Here is the revised query:
Select SWITCHOFFSET(LastFailedOn, Cast((GMTOFFSET * 60) as int)
from MyTable
ORDER BY MyTable.PrimaryKey

Note: The Primary Key is a Clustered Index. So not only is the result set the same, it is most likely returned in the same order.

Jan 26 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Wayno | last post: by
2 posts views Thread by John F Dutcher | last post: by
67 posts views Thread by Steven T. Hatton | last post: by
5 posts views Thread by xuatla | last post: by
9 posts views Thread by Jim | last post: by
2 posts views Thread by akhilesh.noida | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.