Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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