PLz help to fix the error

  • Msg 242, Level 16, State 3, Line 23

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

    I am getting above error, i googled it but i cant find exact solution.

    If any one face this error plz explain. That would be great.

    Thank you.

  • Simply put, one or more of the varchar values you are converting to datetime won't convert - it's just a case of bad data. A value like '1/1/10000' looks like a date, but won't convert to a valid one.

    If you are converting a specific value, please paste your statement here so we can take a look. If you are performing work on a table, then you've got some work to do... you're going to have to find the bad data in the table so you can correct it.

    If it's a small table, then it's easy. But if it's a large table, I would suggest you perform you conversion on small chunks of the table, looking for where the error occurs.

    Something like...

    SELECT convert(datetime, [your_column])

    FROM YourTable

    WHERE id BETWEEN 1 AND 50

    ...then between 51 and 100, and so on. You could also write a loop to test every row, then report which one(s) throw the error.

    Eddie Wuerch
    MCM: SQL

  • It seems like you're trying to compare a varchar and a datetime data type.

    Since the datetime data type has a higher precedence than varchar, the varchar value needs to be converted into a datetime value.

    Please review your code, especially line 23.

    That's about all I can tell without the actual query and the table defs involved.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • update dbo.[sample]

    set platform_id= 'REI', creation_dt= '2009-02-29'

    where Row_id between 144000 and 144100

    for this statement i got above error

    i am trying to insert values in the table for big table

    i am able to insert up to 144000, but after this i am not able to insert date.

  • update dbo.[sample]

    set platform_id= 'AVMselect', creation_dt= '2009-02-7'

    where Row_id between 131001 and 132000

    update dbo.[sample]

    set platform_id= 'Realquest', creation_dt= '2009-02-2'

    where Row_id between 132001 and 136000

    update dbo.[sample]

    set platform_id= 'RealList', creation_dt= '2009-02-14'

    where Row_id between 136000 and 140000

    update dbo.[sample]

    set platform_id= 'c2d', creation_dt= '2009-02-20'

    where Row_id between 140001 and 142000

    update dbo.[sample]

    set platform_id= 'vector', creation_dt= '2009-02-23'

    where Row_id between 142001 and 144000

    update dbo.[sample]

    set platform_id= 'REI', creation_dt= '2009-02-29'

    where Row_id between 144000 and 144100

    this is the actual set of data

  • (1000 row(s) affected)

    (4000 row(s) affected)

    (4001 row(s) affected)

    (2000 row(s) affected)

    (2000 row(s) affected)

    Msg 242, Level 16, State 3, Line 23

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

    The statement has been terminated.

  • Are you aware the year 2009 did not have a February 29th?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • update dbo.[sample]

    set platform_id= 'REI', creation_dt= '2009-02-29'

    where Row_id between 144000 and 144100

    The last batch of transation is firing the error as Feb 2009 in not leap year.

  • thanx for your clue

Viewing 9 posts - 1 through 8 (of 8 total)

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