error in convert datetime from a text field

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."

what can be done to atleast skip those bad data records which 开发者_StackOverflow社区cannot be converted? I tried my best to figure this out but failed. Any help/advice appretiated!

Your ISDATE in the where clause does not necessarily filter out the bad dates before they are used in the conversion.

I think you should do this in two steps. First create a temp table or table variable that holds the rows from STUDENT where startDate and endDate is correct (use ISDATE for this) and then use that table in your actual query.