Re: Help with date comparison
From: <Will>
Date: 1995/07/22
Message-ID: <3upmgu$189_at_data.interserv.net>#1/1
Date: 1995/07/22
Message-ID: <3upmgu$189_at_data.interserv.net>#1/1
You probably cannot use the to_date function at all to solve your problem.
The error you reported is caused when you give Oracle an invalid date.
This happens on Feb 29, which is valid for some years, but invalid for others. It also happens if the data in the table is just plain wrong.
The following code should work:
select 'x' from table where decode( substr(your_date, 4, 3),
'Jan', '01', 'Feb', '02', 'Mar', '03', ...
'Dec', '12', '13') || substr(your_date,1,2)
< decode( substr(compare_date, 4, 3),
'Jan', '01', 'Feb', '02', 'Mar', '03', ...
'Dec', '12', '13') || substr(compare_date,1,2);
Keep in mind, that you may also have to handle special cases, like if the month is there, but the day is missing. Received on Sat Jul 22 1995 - 00:00:00 CEST