Re: checking the valid date string

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/ Received on Thu Dec 21 2000 - 02:40:17 CST