Re: Help with date comparison

In article <3ufd8n$95i_at_newsbf02.news.aol.com>, rs176_at_aol.com (Rs176) writes:
|> 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
Date format strings are case sensitive. to_date(mydate, 'DD-MON') will expect the month to be in all caps, which it apparently isn't. Try using 'DD-Mon' for the format string everywhere, that ought to do it (if this is the problem). Received on Tue Jul 18 1995 - 00:00:00 CEST