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

Question

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.

Answer 1

Without adding a function, a simple predicate

TO_CHAR(date_col,'YYYYMMDDHH24MISS') = '000000000000'

appears to be satisfactory to identify corrupted values stored in an Oracle DATE column. The addition of a function appears to be unnecessary. Checking for corrupted dates should be able to be done in a SQL SELECT statement, and not require a user to have CREATE FUNCTION privilege on the database.

Answer 2

This is a pretty unusual scenario (although I have come across something similar once before). The more common problem is finding invalid dates which are held as strings in a date column. You could adapt the solution for that to your situation, by building your own date validator.

Something like this:

create or replace function is_a_date 
    ( p_date in date )
    return varchar2
is
    d date;
begin
    d := to_date(to_char(p_date,  'SYYYYMMDDHH24MISS'),  'SYYYYMMDDHH24MISS') ;
    if d != p_date then
        return 'not a proper date';
    else
        return 'good date';
    end if;
exception
    when others  then
        return 'not a date';
end;
/ 

This converts a date into a string and back again. It catches exceptions thrown by date casting. If the end product is not the same as the input date then presumably something got lost in translation; to be honest I'm not sure whether the 12011 date would cast successfully to a string, so this is a belt'n'braces approach. It's a bit tricky writing this utility without some test data!

This query would identify all the non-valid dates:

 select h.id, dump(h.bid_close_date)
 from mytable h 
 where h.bid_close_date is not null
 and is_a_date(h.bid_close_date) != 'good date';