I deployed a view into production last night, and this morning the view was failing. Yesterday it was not. The issue?
When doing date arithmetic using the interval datatype.
So yesterday being July 30th, the line in the query in the view has a WHERE clause and part of the clause is this:
report_date >= sysdate - interval '1' month
This ran fine yesterday. But today (07/31) it failed with the Oracle error: ORA-01839: date not valid for month specified.
Simple test is run these 2 SQL statements. First runs fine, second does not.
You can also just use sysdate or systimestamp (but only for today 07/31, else tomorrow it will run fine and not produce the error).
SQL
select timestamp '2020-07-30 00:00:00' - interval '1' month as syst_month
from dual;
select timestamp '2020-07-31 00:00:00' - interval '1' month as syst_month
from dual;
Output
SYST_MONTH
06-30-2020 00:00:00.000000000
ORA-01839: date not valid for month specified
So now my question is.....is THIS a bug in Oracle? If so, is there an MOS (My Oracle Support) ticket for this bug?
Or am I using the interval date datatype incorrectly?
Running on Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Thanks,
Message was edited by: jaramill - Added the database version that I am running on.