We help IT Professionals succeed at work.
Get Started

SQL DateTime convert issue

mcorrente asked
Last Modified: 2015-10-01
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.
Watch Question
Topic Advisor, Page Editor
Distinguished Expert 2018
This problem has been solved!
Unlock 1 Answer and 14 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE