Skip to Main Content


How can I trap an impossibly large date?

dbompFeb 26 2016 — edited Feb 29 2016

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);


    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;


    when BAD_YEAR then

      thedate:= null;

    when others then



...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, 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.

This post has been answered by Frank Kulash on Feb 26 2016
Jump to Answer
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 28 2016
Added on Feb 26 2016