Re: 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.)

<snip>
>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

The to_date function will append the current year to the DD-MON that you supply, thus 08-Dec becomes 08-Dec-95. This means that 29-Feb is not valid for month specified (1995 is not a leap year).

I would try a query like

	select distinct my_date 
	from mytable
	order by mydate desc

This will place all end of months at that beginning of the list and you can then check if 29-Feb, 30-Feb, 31-Feb, 31-Apr, 31-Jun, 31-Sep, 31-Nov is in the list, as all of these will cause failure. Of course, if you see any of the dates with a day > 31 then it will also fail.

I would recommend a before insert trigger (assuming Oracle7) that tries to convert mydate to a date and trap the ORA-01839 error then raise an application error informing the user to enter a valid date. I would also add the validation to the endusers application, if possible.

Hope this helps.

-- 
McKendry's Uncertainty Principle:
At any given moment, one cannot be certain that any opinion is mine--much 
less SSC's or any client's.

Keith McKendry   |  73071.3233_at_compuserve.com
SSC,  Inc.       |