I have a subquery which converts a text coloumn into datetime. Since it is in text format there are coloumns wich contains bad data. I know the first answer would be to correct the data, I strongly agree that. I do not have the privileges to do that, unfortunately i have to deal with it.
below is my query
when i compare e.date with the startdate and enddate it fails giving me an well know error
I also tried to work something like
This didnt workout as well. When i closely looked my coloumns endDate and startDate this is what i could find
what can be done to atleast skip/overcome those bad data records which cannot be converted/compared? I tried my best to figure this out but failed. Any help/advice appretiated!
asked Sep 02, 2011 at 10:16 AM in Default
Given that you can't guarantee that the data presented is a valid date for SQL Server's purposes, then I would suggest looking at slicing & dicing the data.
From what you've shown, the first two parts of the date are OK, it's just the year that's causing problems. In which case, I would suggest checking that
Maybe create a temporary table, or a CTE, or a view that's populated only with data that matches the criterion above, and using that for the rest of your query.
Sorry - can't give you more time on this now as I've got to go out. Perhaps one of m'learned colleagues will take over from here... :-)
answered Sep 02, 2011 at 10:31 AM