Re: Need function "is_date" for converting varchar2 to date-format

> I *think* I would write is_date as
>
> create or replace function is_date(dt IN varchar2,fmt IN varchar2) return
> number
> is
> d date;
> begin
> d := to_date(dt,fmt);
> return 1;
> exception
> when others
> return 0;
> end;
> /
>
> where dt is the string and fmt a valid date format.

Why return 1 or 0? Why not returning the date itself? This way you'll have to write your queries like this:

select my_date from my_table where is_date(my_date,fmt)=1 union
select null from my_table where is_date(my_date,fmt)=0

or something like this.

just change the cod above to:

create or replace function is_date(dt IN varchar2,fmt IN varchar2) return  date
 is
 d date;
 begin
 d := to_date(dt,fmt);
 return d;
 exception

     when others
         return null;

and you have:

select is_date(my_date,fmt) from my_table Received on Wed May 26 2004 - 05:49:25 CDT