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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/07/2003 03:20 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
