sql server - Comparing dates stored as varchar -

i need compare dates stored in database varchar against today's date.

specifically, need exclude records date has passed.

i tried:

select * tblserviceusersschedule  scheduleenddate !='' , scheduleenddate < '2015/05/31' 

this selected values such 17/06/2012 , 19/04/2015, have both passed, along 01/06/2015 hasn't.

i tried cast data with:

select *    tblserviceusersschedule   cast(scheduleenddate datetime)  < cast('05/31/2015' datetime) , scheduleenddate !='' , scheduleenddate not null 

but got following error:

the coversion of varchar data type datetime data type resulted in out-of-range value.

i checked data behind , none null, none blank white space. dates in format of dd/mm/yyyy.

i can't figure out how compare varchar date stored todays date.

storing date values varchar wrong.

if possible, should alter table store them date data type.
can in few simple steps:

1. rename current columns (i'm guessing schedulestartdate varchar) columnname_old. can done using sp_rename.

2. use alter table add columns appropriate data type.

3. copy values old columns new columns using update statement. since of dates stored in same format, can use convert this: set schedulestartdate = convert(date, nullif(ltrim(rtrim(schedulestartdate_old)), ''), 103) if sql server version 2012 or higher, use try_convert. note i've used nullif, ltrim , rtrim convert values contains white spaces null.
4. drop , recreate indexes referencing these columns. simplest way right-clicking index on ssms , choose script index as -> drop , create.
5. use alter table remove old columns.

note: if these columns being referenced in other objects on database have change these objects well. includes stored procedures, foreign keys etc`.

if can't change data types of columns, , sql server version lower 2012, need use convert this:

select * tblserviceusersschedule  convert(date, nullif(scheduleenddate, rtrim(ltrim('')), 103)        < cast(getdate() date); , scheduleenddate not null 

note if have single row column's data not in dd/mm/yyyy format raise error.

for sql server versions 2012 or higher, use try_convert. function return null if conversion fails:

select * tblserviceusersschedule  try_convert(date, nullif(scheduleenddate, rtrim(ltrim('')), 103)       < cast(getdate() date); , scheduleenddate not null 

note: i've used cast(getdate() date) remove time part of current date. means records scheduleenddate @ least 1 day old. if want records scheduleenddate today, use <= instead of <.

one final thing: using functions on columns in clause prevent sql server use indexing on these columns.
yet reason why should change columns appropriate data type.