Oracle DB Ora-01839: date not valid for month specified. 29-02-2016 leap year

Question

We all know today is a special day. Its the 29th of February 2016 of a leap year.

We receive an error message from some tables in our Oracle DB. The error is:Oracle ORA-01839: date not valid for month specified.

For example a simple select where the error occurs:select * from table where table_date > sysdate -0.1;

For other tables this select makes no problem, just for some of the tables.

Is there a way to fix this issue? Because we are not able to use many tables today.

We are using Oracle 12c.

Answer 1

After intensive research, its clear why some of our selects does not work today. The error is caused by the keyword interval and its a known issue. (Or it's how the ANSI/ISO spec says it should work, bottom of page 205/top of page 206)

Here is a qoute from the oracle community blog:

Question:

select to_date('2012-feb-29','yyyy-mon-dd') + interval '1' year as dt from dual;

ORA-01839: date not valid for month specified
01839. 00000 -  "date not valid for month specified"
*Cause:    
*Action:

select to_date('2012-feb-29','yyyy-mon-dd') + interval '2' year as dt from dual;

ORA-01839: date not valid for month specified
01839. 00000 -  "date not valid for month specified"
*Cause:    
*Action:

select to_date('2012-feb-29','yyyy-mon-dd') + interval '3' year as dt from dual;

ORA-01839: date not valid for month specified
01839. 00000 -  "date not valid for month specified"
*Cause:    
*Action:

select to_date('2012-feb-29','yyyy-mon-dd') + interval '4' year as dt from dual;

29-FEB-16 00:00:00


select to_date('2012-feb-29','yyyy-mon-dd') + interval '1' day as dt from dual;

01-MAR-12 00:00:00

select to_date('2012-feb-29','yyyy-mon-dd') + interval '1' month as dt from dual;

29-MAR-12 00:00:00

Answer:

That's just how INTERVALs work. Leap years are the least of the problem; adding 1 month to March 31 results in the same error. If you want to make sure that the result is a valid DATE, then use ADD_MONTHS. (There's no separate function for adding years; use ADD_MONTH (SYSDATE, 12*n) to get the DATE that is n years from now.)

---

Why it happens in our case:

In our case, we used virtual private database for some of our tables because of security reasons. And there we applied the interval keyword in most of the selects.

What to do instead:

Use ADD_MONTHS instead.

select add_months(to_date('2012-feb-29','yyyy-mon-dd'), 12) as dt from dual;