Post reply

An invalid datetime value was encountered

  • February 4, 2004 at 12:35 pm

    #161109

    Has anyone ever received the following error message before?  It occurs sporadicaly in my function but the funny thing is, I don't even have a datetime var in my function.

    Server: Msg 542, Level 16, State 1, Procedure udf_show_this, Line 8

    An invalid datetime value was encountered. Value exceeds the year 9999.

    Thanks in advance,

    Billy

  • February 4, 2004 at 1:53 pm

    #492795

    Is your function doing any conversion of data to DATETIME?

     

    -SQLBill

  • February 4, 2004 at 3:55 pm

    #492803

    No datetime conversions..

    here is the code...

     

    CREATE FUNCTION udf_show_this (@tblOrder_ID INT) RETURNS VARCHAR(8000) AS

    BEGIN

       DECLARE @strMsg VARCHAR(8000)

       DECLARE @i TINYINT

       SET @i = 0

       SELECT @strMsg = 'x' + CAST(@tblOrder_ID AS VARCHAR(8)) + 'x', @tblOrder_ID = tblOrder_fk_ID FROM tblOrder WHERE tblOrder_ID = @tblOrder_ID

       WHILE @tblOrder_ID <> 0

          BEGIN

             SET @i = @i + 1

             SELECT @strMsg = 'x' + CAST(tblOrder_ID AS VARCHAR(8)) + @strMsg, @tblOrder_ID = tblOrder_fk_ID FROM tblOrder WITH (NOLOCK) WHERE tblOrder_ID = @tblOrder_ID

             IF @i > 10

                BREAK

          END

       RETURN (@strMsg)

    END

    thanks in advance,

    Billy

  • February 4, 2004 at 4:18 pm

    #492804

    Could it be bad data?

  • February 4, 2004 at 5:18 pm

    #492811

    The data is fine.  There is only one datetime field in the table and that is the created date field.  I have no idea what else is there except unproven conspiracy theories like maybe the query optimizer and the table are working together secretly to make me lose my mind

    The only pattern I noticed was that it only occurs on first execution of the function right after the function is dropped and recreated.

    That is, if I drop/recreate the function and then execute it, then there is a 5% chance it will throw the invalid datetime value error.

    If I were to execute the function again after the first time, there is a 0% chance it will throw the invalid datetime value error.

    Billy

  • February 4, 2004 at 5:50 pm

    #492816

    Did you run dbcc checktable to the table? You may drop the function, clear out buffer by DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE and recreate the function?

  • February 4, 2004 at 6:03 pm

    #492820

    Actually I haven't tried that.  I will give it a shot.

    Many thanks

    Billy

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply