Post reply

Msg 242 When Validating Date Value

  • December 3, 2004 at 8:13 am

    #60022

    I have a table with a field of datetime(8) type. I have a stored procedure that recieves a parameter declared as DATETIME. The idea is to query my table to see if the date value passed to the sp exists within the table. However, when I run the sp and type in 25/12/2004 for example I get a Msg 242 notification?!

    Can anyone tell me why this is happening? The message refers to converting a char value to date but the data type within the table is datetime and the parameter is a datetime type.

     

    Thanks

     

  • December 3, 2004 at 9:32 am

    #532499

    http://support.microsoft.com/default.aspx?scid=kb;en-us;47048

     



    A.J.
    DBA with an attitude

  • December 3, 2004 at 4:23 pm

    #532555

    Hi A.J,

    Thanks for the response but here is what I'm actually doing:

     @Date_DT DATETIME  AS
    DECLARE @Cnt INT
    SET @Cnt = (SELECT COUNT(*)

    FROM Diary_T

    WHERE Diary_Date_DT = @Date_DT)

    PRINT @Cnt

    When I execute this I get:

    Server: Msg 242, Level 16, State 3, Line 3

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    As you can see, the @Date_DT parameter is already of DATETIME type so where is the char data type conversion happening?

    Thanks

    David

  • December 3, 2004 at 5:32 pm

    #532556

    may be the value thats coming in is not right..check that.besides that i dont see anything wrong with the code..

    hth

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • December 6, 2004 at 5:48 am

    #532615

    Dinakar,

    You were right, the parameter should have been in format YYYY-MM-DD not DD/MM/YYYY! It works perfectly now!

Viewing 5 posts - 1 through 4 (of 4 total)

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