Having a real problem with date fields in Oracle. Executing the following works:
ALTER SESSION set NLS_DATE_FORMAT ='DD-MON-YYYY';
select * from mytable where id = 1;
However, changing the NLS_DATE_FORMAT
causes the query to fail.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
select * from mytable where id = 1;
I end up with a date specific error.
ORA-01858: a non-numeric character was found where a numeric was expected
Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model.
Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.
The table has four date fields, and I've been able to narrow down the fault to certain instances where one specific field is not null, confidental_until
.
If I dump this date field, I don't get any evidence of it being corrupt
select dump(confidential_until) from mytable where id = 1;
Typ=12 Len=7: 120,107,12,31,1,1,1
What could be going wrong? Only 187 records of 420,000+ rows have this problem, with 80,000+ having a non-null confidential_until
field.