none
TODATETIMEOFFSET fails only when reading smallint offset value out of a table

    Question

  • SQL Server 2008 R2 Developer

    I'm getting a completely bizarre failure with the below repro code.  It ONLY fails in scenario #2, where I get "The timezone provided to builtin function todatetimeoffset is invalid."  I can't for the life of me understand what the difference is between scenario #2 and #3 from the function's perspective.  Once the value is assigned to the local SMALLINT variable, why does it matter where the value came from?

    DECLARE	@OrgTimezoneOffset1 INT,
    	@OrgTimezoneOffset2 SMALLINT,
    	@OrgTimezoneOffset3 SMALLINT
    
    CREATE TABLE dbo.Timezone (
    	TimeZoneName VARCHAR(100),
    	TimeZoneOffset SMALLINT
    )
    
    INSERT INTO dbo.Timezone
    SELECT	'(UTC-07:00) Arizona',
    	-420
    
    SELECT @OrgTimezoneOffset1 = tz.TimezoneOffset
    FROM dbo.Timezone tz
    WHERE tz.TimezoneName = '(UTC-07:00) Arizona'
    
    SELECT @OrgTimezoneOffset2 = tz.TimezoneOffset
    FROM dbo.Timezone tz
    WHERE tz.TimezoneName = '(UTC-07:00) Arizona'
    
    SELECT @OrgTimezoneOffset3 = -420
    
    SELECT @OrgTimezoneOffset1, @OrgTimezoneOffset2, @OrgTimezoneOffset3
    
    --Scenario #1: INT variable, assigned from table query - works
    SELECT TODATETIMEOFFSET(GETUTCDATE(), @OrgTimezoneOffset1)
    
    --Scenario #2: SMALLINT variable, assigned from table query - fails
    BEGIN TRY
    	SELECT TODATETIMEOFFSET(GETUTCDATE(), @OrgTimezoneOffset2)
    END TRY
    BEGIN CATCH
    	SELECT ERROR_MESSAGE()
    END CATCH
    
    --Scenario #3: SMALLINT variable, assigned from literal - works
    SELECT TODATETIMEOFFSET(GETUTCDATE(), @OrgTimezoneOffset3)
    
    DROP TABLE dbo.Timezone

    It's easy enough for me to change the declaration to be an INT, but I'd really like to understand what's going on here.

    -Jesse

    • Moved by Tom PhillipsModerator Friday, July 02, 2010 5:58 PM TSQL Question (From:SQL Server Database Engine)
    Friday, July 02, 2010 4:27 PM

Answers

  • That looks really bizarre!

    Seems like a bug to me.

    If you do TODATETIMEOFFSET(GETUTCDATE(),CAST(@OrgTimezoneOffset2 AS INT)) then it works.

    But, as you say, it's really weird that Offset3 works, but Offset2 does NOT.

     


    --Brad (My Blog)
    • Marked as answer by KJian_ Thursday, July 08, 2010 10:05 AM
    Friday, July 02, 2010 6:37 PM
    Moderator
  • Definitely looks like a bug.

    If you add the statement "SET @OrgTimezoneOffset2=CAST(@OrgTimezoneOffset2 AS int)" before the BEGIN TRY, then the error goes away.

    -- 

    Gert-Jan

     

    • Marked as answer by KJian_ Thursday, July 08, 2010 10:05 AM
    Friday, July 02, 2010 10:09 PM
  • Hi Jesse,

    I can re-produce the issue on both SQL Server 2008 SP1 and SQL Server 2008 R2. I suggest you submit a feedback on the Connect site:

    http://connect.microsoft.com/SQLServer/

    It would be great if you can also paste the link to the submitted feedback here, so that other community members can see it as well.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KJian_ Thursday, July 08, 2010 10:05 AM
    Monday, July 05, 2010 4:48 AM

All replies

  • That looks really bizarre!

    Seems like a bug to me.

    If you do TODATETIMEOFFSET(GETUTCDATE(),CAST(@OrgTimezoneOffset2 AS INT)) then it works.

    But, as you say, it's really weird that Offset3 works, but Offset2 does NOT.

     


    --Brad (My Blog)
    • Marked as answer by KJian_ Thursday, July 08, 2010 10:05 AM
    Friday, July 02, 2010 6:37 PM
    Moderator
  • Definitely looks like a bug.

    If you add the statement "SET @OrgTimezoneOffset2=CAST(@OrgTimezoneOffset2 AS int)" before the BEGIN TRY, then the error goes away.

    -- 

    Gert-Jan

     

    • Marked as answer by KJian_ Thursday, July 08, 2010 10:05 AM
    Friday, July 02, 2010 10:09 PM
  • Hi Jesse,

    I can re-produce the issue on both SQL Server 2008 SP1 and SQL Server 2008 R2. I suggest you submit a feedback on the Connect site:

    http://connect.microsoft.com/SQLServer/

    It would be great if you can also paste the link to the submitted feedback here, so that other community members can see it as well.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KJian_ Thursday, July 08, 2010 10:05 AM
    Monday, July 05, 2010 4:48 AM
  • We found exactly the same defect attempting to use code, stable for 8 years since SQL 2000, when we attempt to do inserts of fields of type varchar from one table into another.  We reported the defect to microsoft and they are investigating it.  We have a defect case number. 

    The error appears to occur when SMALLINT is present in the schema.  If all SMALLINT's are converted to INT's, presumably the defect disappers. We discovered the bug dissapear intermittently, if you merely add the declaration of 1 or 2 dummy fields as type INT in tables involved in INSERTS into other databases.  In some cases its for both source table and target table, other times it's for target tables only. 

    It seems to behave as if SQL Server's internal logic is "getting ready" for a possible INSERT involving SMALLINT, but it has 'lost the recipe' for how to do so, unless the table also includes at least one INT field, which probably invokes the inclusion of the proper code to support INSERT.

    We do not find this defect in SQL Server 2005 or in SQL Server 2008 (1st release, not SP1).

    Unfortunately, when this work-around represents substantial time to convert, test & certify all your SQL code; convert, debug & certify all dependent user-interface program code; retest all all client databases from the field, then you've got an enormous cost on your hands.

    In effect, SQL Server 2008 SP1 & R2 have DEPRECATED a legacy feature, inserts in tables having SMALLINT fields, except maybe under certain special and lucky conditions which remain undefined to date. 

    If your codebase and customer base investmentst are substantial, we're thinking it's prudent to downgrade to SQL 2005 until MS gets this fixed in a patch or SP1.

     

     

    Saturday, October 02, 2010 4:34 PM