Re: checking the valid date string

Dennis wrote:
>
> Try this. It returns 1 if string is a date or 0 if it is not a date :-)
>
> SQL> create or replace function isdate (p varchar2)
> 2 return number
> 3 as
> 4 d date;
> 5 begin
> 6
> 7 select to_date(p,'dd.mm.yy') into d from dual;
> 8 return 1;
> 9
> 10 exception
> 11 when others then
> 12 return 0;
> 13
> 14 end;
> 15 /
>
> Funktion wurde erstellt.
>
> SQL> select isdate('21.12.00') from dual;
>
> ISDATE('21.12.00')
> ------------------
> 1
>
> SQL> select isdate('12.21.00') from dual;
>
> ISDATE('12.21.00')
> ------------------
> 0
>
> Hope this helps.
>
> Dennis
>
> In article <91sa6g$i41$1_at_nnrp1.deja.com>,
> tentwenty_at_my-deja.com wrote:
> > How can I check the validity of the string for to_date()? I have a
> > procedure which has 3 varchar2's as it parameters, c_yy, c_mm, and
> > c_dd, then I try to call
> >
> > to_date (c_mm || '.' || c_dd || '.' c_yy, 'mm.dd.yy')
> >
> > when the inputs are valid, it works like charm, but how can I check
 the
> > out of range month and day parameters? month is easy, but what about
> > the day? I tried to do a reasonable EXCEPTION catch, but the
 following
> > won't work either.
> >
> > begin
> > select to_date (c_mm || '.' || c_dd || '.' c_yy, 'mm.dd.yy')
> > into d_date
> > from dual;
> > exception
> > when others then
> > -- do something here
> > end;
> >
> > can anyone help?
> >
> > thanks in advance
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
> Sent via Deja.com
> http://www.deja.com/

Just issuing

begin
  x := to_date(...);
  return 'VALID';
exception when others then
  return 'INVALID'
end;

will be far more efficient than having the sql call.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"