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

Question

We all know today, this is a special day. His 29th of February 2016 leap year .

We get an error message from some tables in our Oracle database. Error: Oracle ORA-01839: date not valid for month specified .

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

For other tables, this choice does not cause problems, just for some tables.

Is there any way to fix this problem? Because today we cannot use many tables.

We are using Oracle 12c .

Answer 1

After intensive research, it became clear why some of our selections do not work today. The error is caused by the interval keyword and its known problem. (Or is it, as the ANSI / ISO specification says, that it should work, at the bottom of page 205 / at the top of page 206)

Here is the qoute from 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

This is how INTERVAL works. Leap years are the least problem; adding 1 month to March 31 leads to the same error. if you want the result to be a valid DATE, then use ADD_MONTHS. (There is no separate function for adding years; ADD_MONTH (SYSDATE, 12 * n) to get a DATE that is n years old.)

---

Why does this happen in our case:

In our case, we used a virtual private database for some of our tables for security reasons. And there we applied the interval keyword to most of the selected ones.

What to do:

Use ADD_MONTHS instead.

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