I know, I know... but I swear I've googled and I can't figure this out.
I have a query that attempts to filter a table based on a date value in one of the fields (so all records where mydate > '2015-09-28'). Unfortunately the field is a varchar field and can't be changed. So what I did is create a subquery filtering the records where ISDATE(mydate)=1. In theory this should give me all records where that field contains a date value, and that seems to work.
The problem occurs when I try to filter by that date. I tried adding CONVERT(DATETIME,mydate) > '2015-09-28' but get a "Conversion failed when converting date and/or time from character string." error. This doesn't appear to be an issue with the '2015-09-28' because I can add CONVERT(DATETIME,'2015-09-28') to the SELECT statement and it converts that value just fine. I also tried using that conversion in the WHERE statement as well with the same issue.
Here's where it gets weird. I figured I'd try to add CONVERT(DATETIME,mydate) to the SELECT statement of the subquery so that I have the already converted date field to work with in the main query. Still got the same error, so I figured that somehow, despite filtering for ISDATE(mydate)=1, an invalid date was getting through and wouldn't convert. I tried to add a limiter to the subquery - TOP 100. Everything worked just fine, which seemed to confirm my thought that an invalid value was sneaking through. I figured I'd keep increasing the range (TOP 100, TOP 1000, TOP 10000) so I could narrow down and find the offending record. Funny thing is, I'm now at TOP 300000 and no error occurs. That's weird, because the table only has about 125000 records.
So, for now, I have the query working by limiting the subquery to 300000 records (out of 125000), which of course covers all my data but isn't really clean. I'd rather solve the problem, and hoping somewhere here can help.