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

"x" <x_at_x.hr> wrote in message news:c91sl8$6jt$1_at_ls219.htnet.hr...
> > 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?

Sorry i'm late seeing this

Because I'm not writing a replacement to_date, I'm writing a function that will test a character string to see if it meets a supplied format. I understood that to be the requirement.

>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;
> end;
>
> and you have:
>
> select is_date(my_date,fmt) from my_table
>
>
>
>
Received on Sat Jun 05 2004 - 07:02:32 CDT