I have this issue that happens a few times a year where an invalid date gets inserted (or so it seems) into a DATE column. However this date is seen differently based on whatever tool you are using to query the data:
The bad date is 4/31/17 (Note 4/31 does not exist, it should be 5/1)
In SQL Plus:
SQL> select * from ktimov.job;
JOB_DELIV
---------
31-APR-17
In SQL Developer:
select * from ktimov.job;
JOB_DELIVERY_DATE
-----------------
01-MAY-17
In DB Artisan:
select * from ktimov.job;
JOB_DELIVERY_DATE
4/30/2017
This is an 11.2.0.4 database running on RHEL 6.4.
Any ideas on maybe why sql plus on the server that is running the database is showing this invalid date vs if I query locally on my SQL Developer?
The reason we catch this bad date is because golden gate is installed on this same server and it is replicating this date to a target database, and golden gate will abend with the following error when this invalid date is detected:
OCI Error ORA-01839: date not valid for month specified (status = 1839)
Our workaround is to issue an update statement setting the date to 5/1 (which sql developer already sees 5/1) and then restart golden gate and it will process.