How to identify invalid (corrupted) values stored in Oracle DATE columns

Oracle 10.2.0.5

What is the easiest way to identify rows in a table that have "invalid" values in DATE columns. By "invalid" here what I mean is a binary representation that violates Oracle rules for date values.

I recently had an issue with an invalid date stored in a column.

I was able to use a query predicate to find a particular problematic row:

  WHERE TO_CHAR(date_expr,'YYYYMMDDHH24MISS') = '00000000000000'

In the case I had, the century byte was invalid...

 select dump(h.bid_close_date) from mytable h where h.id = 54321

 Typ=12 Len=7: 220,111,11,2,1,1,1

The century byte should be 100 + two digit century. In this case, there was an extra 100 added, as if the century value was "120", making the year "12011". (The only way I know to get invalid DATE values into the database is using OCI, using native 7-byte DATE representation.)

In this case, the TO_CHAR function returned an identifiable string, which I could use for identifying the wonky DATE value.

My question: is there an more general or easier approach (preferably using a SQL SELECT statement) to identify rows with "invalid" values in DATE columns.

Solution

This identifies invalid months

SELECT rowid,
       pk_column,
       DUMP(date_column, 1010) AS dump1
FROM   table
WHERE  TO_NUMBER(SUBSTR(DUMP(date_column, 1010), INSTR(DUMP( date_column, 1010),
                                              ',', 1, 2
                                                     ) + 1,
                                  INSTR(DUMP(date_column, 1010), ',', 1, 3) - (
                                  INSTR(DUMP( date_column, 1010), ',', 1, 2) + 1
                                  ))) = 0;

Update using the same where clause, I found the month number was zero in these cases.