Corrupt date fields causing query failure in Oracle

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.

Best Answer

The date looks fine: 

SQL> CREATE TABLE testdate (d date);
Table created
SQL> INSERT INTO testdate
  2     VALUES (to_date(to_char((120-100)*100+107-100)||'-12-31','yyyy-mm-dd'));
1 row inserted
SQL> 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

Related Question