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
[snip]
>
> 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.
>

Could easily be

select decode(is_date(my_date),0,null,my_date) from my_table

No need to throw union at this.

In general best to allow a function to return a value that represents only
a single idea, rather than either a A) date or B) NULL if invalid, which requires further processing to deal with. Also - functions that hide invalid data can be dangerous - I think this is what I fear most with this method.

Of course it's best of all to store dates as dates and allow Oracle to deal with the dates.

Greg

>
> 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 Wed May 26 2004 - 14:00:19 CDT