Casting varchar as date

Question

I think I've read just about every thread on this topic and none of them are solving my problem.

First of all, the database I'm working with has all date fields set to a varchar data type, which drives me insane because I constantly have to cast or convert whenever I'm working with any queries involving dates.

The query I'm working with at the moment is supposed to pull a list of medical patients who all have a certain diagnosis, but that diagnosis has to have been given before a certain date. Here's what I've got.

SELECT DISTINCT
    pd.PatientID,
    pd.PatientName,
    pmh.DateOfOnset
    pmh.DiagnosisCode
FROM PatientDemographic pd JOIN PatientMedicalHistory pmh ON pd.PatientID = pmh.PatientID
WHERE pmh.DiagnosisCode = '401.1'
    AND CAST(pmh.DateOfOnset as Date) > CAST('12/31/2014' as Date)

I'm getting an error that says "Conversion failed when converting date and/or time from character string." It's telling me the error is on Line 1 though (the SELECT DISTINCT line) so that's not really helpful and I'm not sure what I need to fix.

As I mentioned, the DateOfOnset field has a varchar data type, and I need to find any of those dates that came before the end of December, 2014. I've tried to remedy this error by trying different combinations of the CAST statements -- I even tried including a cast on the date field in the SELECT statement, and I'm still getting the same error.

I was working on another query earlier that required me to find all patient appointments from within a certain time frame, and for that query, I had my WHERE clause set up like:

WHERE Cast(VisitDate as Date) BETWEEN CAST('01/01/2014' as Date) AND CAST('12/01/2014' as Date)

...and it worked perfectly fine. Since I've got my current query set up virtually the same way, I'm not sure why I'm getting that conversion error.

Solution 1

You have wrong dateformat:

SET DATEFORMAT dmy;
SELECT CAST('12/31/2014' as Date);
--Conversion failed when converting date and/or time from character string.

You could set it to mdy before executing your query.

SET DATEFORMAT mdy;
SELECT CAST('12/31/2014' as Date);

LiveDemo

or use CONVERT with style 101:

SET DATEFORMAT dmy;
SELECT CONVERT(DATE,'12/31/2014',101)

---

If you really need to store DATE as VARCHAR use at least culture independent type like ISO-8601.

SET DATEFORMAT dmy;
SELECT CAST('20140201' as Date);
-- 01.02.2014

SET DATEFORMAT mdy;
SELECT CAST('20140201' as Date)
-- 01.02.2014

Solution 2

It sounds like SQL is not able to convert the stored strings into dates. This would explain why CAST(pmh.DateOfOnset as Date) fails where Cast(VisitDate as Date) does not--the latter might not have any mis-formatted dates.

Best-case solution is to convert your table columns to the proper datatypes. Second-best case, add columns containing the proper datatypes, and convert the data over; you'd have to fix any existing bad data, as well as convert data on the fly as it's loaded (yuck). Another option, add a calculated column, though you'll have problems with the afore-mentioned invalid dates. (What version of SQL do you have? Later versions have the isdate function, which might help here.)

If modifying tables is not an option, you're probably stuck writing queries that have to assume some of the data is invalid (bad format).

Comments

[GendoIkari]

The error is happening on line 1 because line 1 is the beginning of the statement that contains the error. The error happens when running the select statement.

[EJF]

Yeah, I'm probably stuck with the latter option. The database is connected to a medical record system and all the dates get entered as strings because they're from free text fields in the system (no clue what genius set it up that way, but it has left the door open for a LOT of bad data in many different tables). I may just search for ALL diagnoses and paste the results into Excel and filter by everything from 2014 and earlier. Not super efficient, but neither is wasting time combating errors I can't fix...

[EJF]

Update: yeah, after looking at it again, it looks like there are quite a few dates that are just blank. That's probably what's screwing me up.

[Philip Kelley]

If dates are blank, can you just ignore them, or use a default date value? (today, 1/1/1980, or some such)

[EJF]

I'll try excluding all the blanks and see what happens!