Help with date comparison

I'm hoping someone can help solve a problem I'm having comparing dates. I have a table with a VARCHAR2 column holding dates in the format 'DD-Mon'. That is, one value is the string '17-Jul', another is '10-Dec', etc. (I must use VARCHAR2; much as I'd like to, I can't change it to DATE.)

I would like to select all records from this table that have a date that is between a specific range. In the following attempt, I want to find all records with dates between 8-Dec and 10-Dec ('mydate' is the VARCHAR2 column):

select * from mytable

where to_date(mydate, 'DD-MON') >= to_date('08-Dec', 'DD-Mon')            
      
and to_date(mydate, 'DD-MON') <= to_date('10-Dec', 'DD-Mon');             
     

However, this results in the following error:

   ORA-01839: date not valid for month specified

I've tried many other approaches (I won't fill up the newsgroup by listing them), with no success. Can anyone recommend a solution?

Thanks.

->Russ
Received on Tue Jul 18 1995 - 00:00:00 CEST