How to ignore bad characters in SQL query

Question

Short story: I have SQL Server DB with varchar fields instead of datetime (don't ask, this is a long story and cannot be fixed). Somehow recently we got weird / random characters inserted in these fields, and not what should be there (NULL, '' or YYYY-MM-DD). Something like this: "? + X" with high-order ascii characters.

The report uses this query to help massage the data into something useful (only the relevant parts posted here):

SELECT CASE WHEN c.CallStatus = 'Closed' THEN CAST(c.ClosedDate + ' ' + c.ClosedTime as datetime) ELSE NULL END as 'Closed Date'
WHERE CAST(c.closeddate AS DATETIME) BETWEEN  @StartDate AND @EndDate

but he is choking on this new bad data.

My question is this:

How can I update a query to ignore bad data so that I can run reports while I look for the source of bad data? My first priority is to make reports function, and secondly, to find and kill the source of bad data.

Answer 1

CASE WHEN ISDATE(closeddate) = 1 THEN CAST(c.closeddate AS DATETIME) ELSE NULL END

Answer 2

Everything seems to be fixed, thanks! here is my final result:

And case WHEN ISDATE (closeddate) = 1 THEN CAST (c.closeddate AS DATETIME) ELSE NULL END BETWEEN @StartDate AND @EndDate

and now on the hunt!