Do a comparison using a date stored as a varhcar

[thorny00]

The e.calc_mostrecenthiredate is stored as a varchar. I have the following as part as my Where statement:
IsNull(convert(datetime,e.calc_mostrecenthiredate,1),'') >= '12/1/2005 00:00:01'
but get the following error:
Syntax error converting datetime from character string.
Warning: Null value is eliminated by an aggregate or other SET operation.

There are nulls in the field and the dates are stored as
10/6/1977
11/23/1981

How can I do this comparison?
Thanks for the help and time!

 

[SQLSister]

You need the ISDate function. If it isn't a date set it to null and then the is null part of the query should take care of it.

This is why you should never store dates in a varchar or other character type of field. Inthe first place nondates creep in and in the second place you have to run all sorts of extra functions to do date math which makes your system less efficient.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief