Ok, try this one. It's a little smarter. :)
create or replace function df1
( date_in varchar2 )
return date
is
x_date exception;
pragma exception_init(x_date, -1830);
v_source_date_format varchar2(20) := 'yyyy-mm-dd';
begin
if owa_pattern.match(date_in,'^\d{4}-\d{2}-\d{2}')
then
null;
else
raise_application_error(-20000,'Hey! Thats a bad date!');
end if;
return to_date(date_in, v_source_date_format);
end;
/
show errors function df1
Jared
Jeremy Pulcifer <Jeremy.Pulcifer_at_kadiri.com>
Sent by: root_at_fatcity.com
01/07/2003 05:38 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: RE: PL/SQL Date Format
Not good, Jared. Try this:
SQL> select df1('01-JAN-03') from dual;
DF1('01-J
03-JAN-01
Oops! I don't know how you could do this other than to parse the string
like you did and look for invalid_num exceptions. Or force the app
software to handle the data entry validation and convert it to a data
format.
> -----Original Message-----
> From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com]
> Sent: Tuesday, January 07, 2003 4:53 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: PL/SQL Date Format
>
>
> How about:
>
> create or replace function df1
> ( date_in varchar2 )
> return date
> is
> v_test_date date;
> x_date exception;
> pragma exception_init(x_date, -1830);
> v_source_date_format varchar2(20) := 'yyyy-mm-dd';
>
> begin
> begin
> v_test_date := to_date(date_in, v_source_date_format);
> exception
> when x_date then
> raise_application_error(-20000,'Hey! Thats a bad date!');
> end;
> return v_test_date;
> end;
> /
>
> show errors function df1
>
> select df1('2003-01-07') from dual;
> select df1('01-07-2003') from dual;
>
>
> Jared
>
>
>
>
>
>
> "Fink, Dan" <Dan.Fink_at_mdx.com>
> Sent by: root_at_fatcity.com
> 01/07/2003 03:20 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: PL/SQL Date Format
>
>
> Okay, PL/SQL programmers, a lowly dba is in need of your
> assistance. If
> you will show pity on my poor self, who does not deserve even
> the mearest
> consideration, I will be greatly indebted...
>
> I have a proc that needs to process a date field. The users
> want to enter
> it in a specific format (YYYY-MM-DD) that is not the same as
> the system
> format (DD-MON-YY). If they do not enter the correct format,
> I need to
> raise an exception. The parameter was set as DATE, but it
> would not allow
> me to enter the requested format, so I changed it to
> VARCHAR2. When it was
> date, it would not accept the requested format. When it is varchar2,
> PL/SQL does an implict conversion of the date. Unfortunately,
> it is an
> incomplete conversion and the date is not correct (see example below).
>
>
> SQL> execute qa_subs.set_expire_date(1,'TEST','01-JAN-01');
> This is set_expire_date
> Expire date is 0001-01-01
>
> SQL> execute qa_subs.set_expire_date(1,'TEST','2001-01-01');
> This is set_expire_date
> Expire date is 2001-01-01
>
> So I added a substr to extract the date and try to convert it
> to numbers.
> Very unelegant...
>
> PROCEDURE set_expire_date ( p_user_group_id IN NUMBER DEFAULT NULL,
> p_product_id IN VARCHAR2 DEFAULT NULL,
> p_expire_date IN VARCHAR2 DEFAULT
> NULL) IS BEGIN
> dbms_output.enable(10000);
> dbms_output.put_line('This is set_expire_date');
> v_expire_year := substr(p_expire_date, 1, 4);
> v_expire_month := substr(p_expire_date, 6,2);
> v_expire_day := substr(p_expire_date, 9,2);
> v_expire_date := to_date(p_expire_date, 'YYYY-MM-DD');
> dbms_output.put_line('Expire date is '||to_char(v_expire_date,
> 'YYYY-MM-DD'));
>
> EXCEPTION
> WHEN INVALID_NUMBER THEN
> dbms_output.put_line('Invalid Date format');
> dbms_output.put_line('Format must be YYYY-MM-DD
> ('||to_char(sysdate,
> 'YYYY-MM-DD')||')');
> WHEN VALUE_ERROR THEN
> dbms_output.put_line('Invalid Date format');
> dbms_output.put_line('Format must be YYYY-MM-DD
> ('||to_char(sysdate,
> 'YYYY-MM-DD')||')');
> END set_expire_date;
>
> Is there a method (other than altering the session before calling the
> proc) to force an input value to be in a certain format? I've
> checked my
> docs and online and I'm drawing a blank.
>
> With Humble regards,
>
> Dan Fink
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: Jared.Still_at_radisys.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in the message BODY, include a line containing: UNSUB
> ORACLE-L (or the name of mailing list you want to be removed
> from). You may also send the HELP command for other
> information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: Jared.Still_at_radisys.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Jan 08 2003 - 12:26:23 CST
