Incorrectly stored dates in a database

What is incorrect date

• You might sometimes find a DATE column in a database with incorrectly stored data like '00-000-0000'
• It can be unexpected errors like ORA-01841
• Or the result is correct but if you try to use it in where clause the result is incorrect

How can these dates end up in the database

• This issue is about your client side code and database provider
• if your client application uses OCI or Pro*C, then you can use the DATE external data type in OCI and Pro*C to insert illegal values into the database

Detection of these incorrect values

Use dump function on this data example (there are to rows looks similiar but their dump is not the same CIl_NCZ_VERILIS_TARIHI is a date cloumn an values kept as dd/mm/yyyy )

select CIl_NCZ_VERILIS_TARIHI,dump(a.CIl_NCZ_VERILIS_TARIHI,16) from aaa a;

30/11/2001 Typ=12 Len=7: 50,63,b,1e,1,1,1

30/11/2001 Typ=12 Len=7: 78,65,b,1e,1,1,1

How to find bad rows in a date column?

To find the rowid's of a date column giving ORA-01841 you can use a approach like:

DECLARE
CURSOR c1 IS SELECT rowid, DUMP (incorrectdate) as DUMP FROM incorrecttable;
v_incorrectdate incorrecttable.incorrectdate%TYPE;
date_error_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(date_error_detected,-1841);
BEGIN
FOR row_rec IN c1
LOOP
dbms_output.put('ROWID: 'row_rec.rowid' DUMP:'row_rec.dump);
BEGIN
SELECT incorrectdate INTO v_incorrectdate FROM incorrecttable WHERE rowid=row_rec.rowid;
dbms_output.put_line(' DATE:'v_incorrectdate);
EXCEPTION
WHEN date_error_detected THEN dbms_output.put_line(' DATE: Got ORA-01841 on this date');
END;
END LOOP;
END;
/