Corrupt date fields causing query failure in Oracle 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.
The date looks fine: 

SQL&gt; CREATE TABLE testdate (d date);
Table created
SQL&gt; INSERT INTO testdate
  2     VALUES (to_date(to_char((120-100)*100+107-100)||'-12-31','yyyy-mm-dd'));
1 row inserted
SQL&gt; SELECT DUMP(d), d FROM testdate;
DUMP(D)                                                                          D
-------------------------------------------------------------------------------- -----------
Typ=12 Len=7: 120,107,12,31,1,1,1                                                31/12/2007


Are you able to use the specific conversion function TO_CHAR on those lines? For instance SELECT to_char(confidential_until, 'yyyy-mm-dd') FROM mytable WHERE id = :x