Link to home
Avatar of mcorrente
mcorrenteFlag for United States of America

asked on

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.
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

>> 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.
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
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'
>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.
Avatar of mcorrente


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.
Avatar of James0628

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.

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

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?
It's always M/DD/YYYY:

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)