sql - Casting varchar as date -

i think i've read every thread on topic , none of them solving problem.

first of all, database i'm working has date fields set varchar data type, drives me insane because have cast or convert whenever i'm working queries involving dates.

the query i'm working @ moment supposed pull list of medical patients have diagnosis, diagnosis has have been given before date. here's i've got.

select distinct     pd.patientid,     pd.patientname,     pmh.dateofonset     pmh.diagnosiscode patientdemographic pd join patientmedicalhistory pmh on pd.patientid = pmh.patientid pmh.diagnosiscode = '401.1'     , cast(pmh.dateofonset date) > cast('12/31/2014' date) 

i'm getting error says "conversion failed when converting date and/or time character string." it's telling me error on line 1 though (the select distinct line) that's not helpful , i'm not sure need fix.

as mentioned, dateofonset field has varchar data type, , need find of dates came before end of december, 2014. i've tried remedy error trying different combinations of cast statements -- tried including cast on date field in select statement, , i'm still getting same error.

i working on query earlier required me find patient appointments within time frame, , query, had where clause set like:

where cast(visitdate date) between cast('01/01/2014' date) , cast('12/01/2014' date) 

...and worked fine. since i've got current query set virtually same way, i'm not sure why i'm getting conversion error.

you have wrong dateformat:

set dateformat dmy; select cast('12/31/2014' date); --conversion failed when converting date and/or time character string. 

you set mdy before executing query.

set dateformat mdy; select cast('12/31/2014' date); 

livedemo

or use convert style 101:

set dateformat dmy; select convert(date,'12/31/2014',101) 

---

if need store date varchar use @ least culture independent type iso-8601.

set dateformat dmy; select cast('20140201' date); -- 01.02.2014  set dateformat mdy; select cast('20140201' date) -- 01.02.2014