SWITCHOFFSET error: "timezone ... is invalid"

• lucash

  SSC Veteran

  Points: 217

  More actions

  January 27, 2009 at 12:09 pm

  #205884

  I'm trying to run the following query:

  SELECT

  [dbo].[Foo].[PersonID]

  ,[dbo].[Foo].[LastFailedOn]

  ,[dbo].[Locations].[GMTOffset]

  ,SWITCHOFFSET([dbo].[Foo].[LastFailedOn], CAST([dbo].[Location].[GMTOffset] * 60 AS INT))

  FROM

  [dbo].[Foo]

  INNER JOIN

  [dbo].[People] ON [dbo].[Foo].[PersonID] = [dbo].[People].[PersonID]

  INNER JOIN

  [dbo].[Locations] ON [dbo].[People].[LocationID] = [dbo].[Locations].[LocationID]

  "LastFailedOn" is a DATETIMEOFFSET value, with every row but one of the Foo table having NULL. "PersonID" is the primary key and clustered index value for both the Foo and People tables. "GMTOffset" is a number of hours stored as a FLOAT; it has no values beyond -11.0 or 11.0. Every row of People has a valid LocationID, and there are no null GMTOffsets in Locations.

  When I run this query, it manages to return almost all of the rows that it should, apparently failing right before the one row that actually has a "LastFailedOn" value. (That is, every row returned has a NULL in the second and fourth columns.) Then it produces this error:

  Msg 9812, Level 16, State 1, Line 1

  The timezone provided to builtin function switchoffset is invalid.

  I can confirm that no row in the set produces a result for "CAST([dbo].[Location].[GMTOffset] * 60 AS INT)" that is not an integer between -660 and 660.

  If ask for rows "WHERE [LastFailedOn] IS NOT NULL", I get the one non-NULL row with no complaints and exactly the result I expect. If I ask for rows "WHERE [LastFailedOn] IS NULL" I get all the other rows with no complaints. When I ask for a range of PersonIDs that includes some NULL and some non-NULL, there are no complaints.

  What gives? Why does this query only fail when there is no WHERE clause?