SQL DateTime convert issue

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>> I tried adding CONVERT(DATETIME,mydate) > '2015-09-28' but get a "Conversion failed when converting date and/or time from character string." error
ok, first of all, what's the format of values saved into this mydate field? knowing this is a varchar field anyway...
Please post the create table directive defining the field in question, then post a sample if the data in that column.

Trying to see whether the format of the date is the issue.
Mark WillsTopic AdvisorCommented:
Have you tried wrapping the ISDATE test in a CASE statement ?

So you check : case when isdate(yourfield) = 1 then convert() else null end as newdate  as part of the subquery (or instead of NULL, try 1900-01-01 maybe) ?

Would be good to see a sample (obfuscated) code segment...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Mark WillsTopic AdvisorCommented:
Here is a quick little example
create table #tmp (id int identity, str varchar(40))

insert #tmp(str) values ('2015-01-01')
insert #tmp(str) values ('Not a Date')
insert #tmp(str) values (NULL)

Select id, str, case when isdate(str) = 1 then 'date' else 'no' end
       ,case when isdate(str) = 1 then convert(datetime, str) else convert(datetime,'19010101') end as newdate
from #tmp

Open in new window

Now, the convert will need to comply with date type and might need format codes... Then (because the column newdate is always a date) you can happily test for dates > '19010101'
Jim HornMicrosoft SQL Server Data DudeCommented:
>Unfortunately the field is a varchar field and can't be changed.
Accepting that means you're going to have to play these games every time there is a need to query the column and do math / comparisons.  

I'd avoid this by createing a new column in datetime format, updating it, then querying that column.   Or if this is an SSIS issue converting in a staging table.
mcorrenteAuthor Commented:
Sometimes you have to deal with the database structure as it's given to you.  Varchar field has to stay that way - it's the backend to client management software that allows customization by using varchar fields for every field on the front end and you define the datatype on the form.

Mark's solution worked and was quick and concise.  I wish my day would allow me to really dig into this because I'm still confused about WHY it works - if I'm filtering everything where ISDATE(myDate) isn't 1, why would it ever hit the ELSE part of the CASE statement?  Unfortunately, too much on my plate.  

Thanks, all.
I'm very much just guessing here, but I wonder if, in trying to optimize the query, MS SQL is adding the date test to the subquery.  I could see some logic in doing that, to try to limit the subquery results to the values that the main query is looking for.  As for why you don't get the error when you use TOP 300000, the obvious guess is that that changes how the query is evaluated/optimized.

Mark WillsTopic AdvisorCommented:
It all has to do with how and where the attempt of using the varchar column as a datetime. It is very easy for the optimizer to look at a complex query and decide some precedents which means that the hope (or want) for SQL to just use datetimes, doesn't happen.

So, always filter out, or convert to a new (correctly typecast) column.

Using the same sample / test data as above, this should exemplify how to retain existing columns for subsequent manipulation :
;with cte_legit_dates as
(select * from #tmp where isdate(str) = 1)
select *, convert(datetime, str) as newdate
from cte_legit_dates

Open in new window

And that is where the CTE works so much better than a traditional subquery - it is more SQL optimizer friendly because it must resolve the CTE before it uses it.

So James's guess above is pretty good :)

One of the biggest gotcha's is NULL in a varchar is NOT the same as NULL in a datetime. The underlying datatype for datetimme is numeric so, James's other assertion for TOP() is also correct.

I wouldn't try to second guess what SQL might do, been burnt  couple of times. So, I always try to cast a result set into correct data types before using them as that data type (and be thorough get a result depicting both an affirmative test and cast a value for a negative test = hence using CASE statement to effectively create a new correctly type cast column)  

Hope that clears it up a bit more...
mcorrenteAuthor Commented:
It does.  Thanks for the insights.
Thanks Mark.  It sounded good, in theory, but I've never really tried to look too much into query optimization, so I really couldn't be sure.

mcorrenteAuthor Commented:
Tried the CTE. Same error.  I still needed the CASE WHEN statement to get it to work.
Do you have a uniform way of data entry of dates, or they vary? I.e. the format is such that either it is not there, or the format is off i.e. year-month-day one one and year-day-month or another (using numeric digits)
Storing dates in string format denies the option for indexing ......
Could you post a sample of the columns entries?
mcorrenteAuthor Commented:
It's always M/DD/YYYY:

Mark WillsTopic AdvisorCommented:
The ISDATE() function respects the SET DATEFORMAT or as set by SET LANGUAGE

SET LANGUAGE us_english

The COVERT() function will probably need to use style codes to tell it that the string is DMY


/* Expression in mdy dateformat */
SELECT ISDATE('9/03/2015'); --Returns 1.
SELECT ISDATE('9/21/2015'); --Returns 1.
SELECT ISDATE('12/03/2015'); --Returns 1.

/*And the CONVERT will need to use style code of 101*/

/* so lets add some more data*/

insert #tmp(str) values ('9/03/2015')
insert #tmp(str) values ('9/21/2015')
insert #tmp(str) values ('12/03/2015')

/* and do the query again under SET DATEFORMAT mdy;*/


;with cte_legit_dates as
(select * from #tmp where isdate(str) = 1)
select *, convert(datetime, str,101) as newdate
from cte_legit_dates

Open in new window

But as I said before, always prefer to use a CASE statement to explicitly create a properly type cast column.

Having said that, you might need to double check your results to make sure they include some of the *other* date values (e.g. check for days > 12 in the results)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.