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.

Answer 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