I have a C# program that's blowing up with a "Year, Month, and Day parameters describe an un-representable DateTime" error reading a date column. If I look at the date in SQL Developer, it says it's "(null)", but if I do a dump(thedate), it says that it's "Typ=12 Len=7: 237,44,7,14,1,1,1", which if I understand it right, is the year 123744. Essentially, the PL/SQL code did this:
create table t (thedate date);
insert into t values (add_months(to_date('27-sep-2006','dd-mon-yyyy'), 618884));
select thedate, dump(thedate) from t;
I'm a little surprised it doesn't blow up because the year is bigger than 9999. My problem is trying to figure out how to trap it in PL/SQL so that the bad data doesn't get out in the first place. I thought something like "if thedate is later than 2100, null it" would work, basically like...
BAD_YEAR exception;
pragma exception_init (BAD_YEAR, -1841);
begin
thedate := add_months(to_date('27-sep-2006','dd-mon-yyyy'), 618884);
if thedate > to_date('01-jan-2100','dd-mon-yyyy') then
thedate := null;
end if;
exception
when BAD_YEAR then
thedate:= null;
when others then
raise;
end;
...but it doesn't raise the exception, and the if-test isn't tripped, as if it's saying that it can't even evaluate thedate in order to compare it to 2100.
So what's the best way to trap and fix this sort of thing? (Oracle 11.2.0.3, if it matters. And of course it's not literally what I have above; the real program has variables and formulas inside that add_months().) Thanky.