Corrupt date fields causing query failure in Oracle

Question

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.

Answer 1

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

Answer 2

On occasion wrong dates (the actual bytes when you look at the DUMP) seem incorrect. I have an example of Months stored not as 1-12 but as 0?

The give away is when you use a session date format mask of DD-MON-YYYY, these Month-0 dates show up as 01-DECEMBER-2000 not the expected 01-DEC-2000.

Oracle Support have a specific note to help you find it.

HowTo validate a date/timestamp column (Doc ID 976591.1)

Frank

Answer 3

It turned out the issue was with a trigger on the confidential_until field, which had a date that was of the format DD/MON/YYYY, and this failed if the session NLS_DATE_FORMAT was YYYY-MM-DD.

The fix was to explicitly define the date format in the trigger.

_{Community Wiki answer created from a comment by the question author, Michael}