Dan,
Look
at the TO_DATE function. You can easily change your procedure to the
following:
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
local_date
date;
== added this
BEGIN
to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
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
Jared,
SELECT df1('2002-13-01') FROM dual; -- :)
CREATE OR REPLACE PROCEDURE set_expire_date (
p_user_group_id IN NUMBER DEFAULT NULL,
p_product_id IN VARCHAR2 DEFAULT NULL,
p_expire_dateIN VARCHAR2 DEFAULT NULL
)
IS
ld_dummy
of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: PL/SQL Date Format
Jared,
SELECT df1('2002-13-01') FROM dual; -- :)
CREATE OR REPLACE PROCEDURE set_expire_date (
p_user_group_id IN NUMBER DEFAULT NULL,
p_product_id IN VARCHAR2
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) := '-mm-dd';
begin
begin
v_test_date := to_date(date_in, v_source_date_format);
Title: 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