In old days and even today many of the programmers declare date filed as varchar in database.
which leads to many invalid entries in the database and incorrect execution of the program.
We can either check at code level before inserting if the date is valid or invalid by ISDATE() Function
Or if we dont do it and there are already couple of wron entries in the DB the Hell starts for the coder to find where exactly the Bug is.
Either we go one by one row or we might check len(date)>8
which gives the dates like 12/121/1985 or 12/12/12895
it could be other way as well like /1/1982.
The Simple way to debug what i think is try to convert the date and see where the issue comes like.
select convert(datetime, date ,103) as DOB,* from TableName
which leads to many invalid entries in the database and incorrect execution of the program.
We can either check at code level before inserting if the date is valid or invalid by ISDATE() Function
Or if we dont do it and there are already couple of wron entries in the DB the Hell starts for the coder to find where exactly the Bug is.
Either we go one by one row or we might check len(date)>8
which gives the dates like 12/121/1985 or 12/12/12895
it could be other way as well like /1/1982.
The Simple way to debug what i think is try to convert the date and see where the issue comes like.
select convert(datetime, date ,103) as DOB,* from TableName
Thsi will give you all the rows and stop immediately after encounntering error .
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Just go to the result pane and then check the last row it showed and broke. You will notice that the culprit is the next ID or date solve it and go ahead !
This was a solution which i use to trouble shoot. please comment if you have a better one.
No comments:
Post a Comment