July 4, 2011 at 3:59 pm
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.
July 4, 2011 at 4:23 pm
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
July 4, 2011 at 4:25 pm
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.
July 4, 2011 at 5:30 pm
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.
July 4, 2011 at 5:32 pm
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
July 4, 2011 at 5:33 pm
(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.
July 4, 2011 at 5:41 pm
Are you aware the year 2009 did not have a February 29th?
July 5, 2011 at 2:05 pm
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.
July 5, 2011 at 4:03 pm
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