asked
compare a datetime with a varchar(30)
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 INNER JOIN TABLE XYZ ON XYZ.COLOUMN1=YZX.COLOUMN2 LEFT JOIN ( SELECT ABC.stu_id ABC.stu_name CONVERT(DATETIME,LMN.startDate,111) STARTDATE CONVERT(DATETIME,LMN.endDate,111) ENDDATE FROM STUDENT ABC INNER JOIN AN_STUDENT_TABLE LMN ON ABC.stu_id=LMN.stu_id WHERE ISDATE(startDate)=1 AND ISDATE(endDate)=1 GROUP BY ABC.stu_id,ABC.stu_name,STARTDATE,ENDDATE) DIN ON DIN.stu_id=LMNOP.stu_id WHERE e.date BETWEEN DIN.STARTDATE AND DIN.ENDDATE when i compare e.date with the startdate and enddate it fails giving me an well know error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." I also tried to work something like instead use CONVERT(DATETIME,CASE WHEN ISDATE(LMN.startDate)=1 THEN LMN.startDate END,111) and same for end date This didnt workout as well. When i closely looked my coloumns endDate and startDate this is what i could find startDate endDate 01/23/1200 01/01/2009 09/14/0210 01/01/2010 12/31/2020 01/01/2009 05/12/1212 09/25/1008 01/01/2002 01/01/1320 10/01/1001 01/01/2009 01/23/1200 10/01/1007 09/08/1010 08/01/0120 01/01/0201 09/22/1009 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!
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 `convert(integer, substring(startdate,7,4)) > 1900` before doing anything else. 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... :-)
