We experienced a full system crash a few weeks ago and had to restore the database from a dump file. This actually happened twice. In between the two restores a job that runs a Stored Procedure overnight began inserting a date in a particular date field with the year and day transposed. ie. if the date was supposed to be 23-JUN-2008 it was stored as 08-JUN-2023.
After the second restore things were back to normal and only the records updated during the initial inbetween period were affected. I have narrowed it down to the following statement in the Stored Procedure.
select to_date(to_char(trunc(sysdate -1))||' 23:59:00','DD-MM-YY HH24:MI:SS'),sysdate
into sysdate1,sysdate2
from dual;
During the problem period whenever the variable 'sysdate1' above is stored to a table in a date field the problem was manifest.
ie.
UPDATE <table>
SET last_modified_date = sysdate1;
WHERE expiry_date = <whatever>
Both the variable sysdate1 and the field last_modified_date are of data type DATE.
Does anyone have any idea of where I can look to explain how this happened? I have been playing with NLS_DATE_FORMAT and language settings in Oracle but to no avail.