How to determine if a character field is a valid date 1

[ddiamond]

I want to use the following date expression in my select query:

to_date(substr(description,1,8),'mm/dd/yy') as deposit_date

This works fine as long as the first 8 characters represent a valid date in 'mm/dd/yy' format. Unfortunately, occassionly the description field contains bad data. In this case I would like to return null for deposit_date, but the above expression fails with an error instead. Any ideas? I know I can do it with PL/SQL and error trapping, but I'm hoping there is an easier way.

 

[SantaMufasa]

DDiamond,

Since native Oracle SQL does not have internal error processing (where PL/SQL does have EXCEPTION handling), I highly recommend your using a user-defined function to do what you want.

Here is a simple, sample function per your request:

create or replace function checkdt (str_in varchar2) return date is
    date_hold date;
begin
    date_hold := to_date(str_in,'mm/dd/rr');
    return date_hold;
exception
    when others then
        return null;
end;
/

Function created.

select checkdt('08/08/08') from dual;

CHECKDT('
---------
08-AUG-08

select checkdt('13/08/08') from dual;

CHECKDT('
---------

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ddiamond]

Thanks Santa. Works great!