Hey, I'm a concepts guy!
He asked for valid formats, not valid dates.
Just combine the code from my 2 posts, and your done.
:)
Jared
Vladimir Begun <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/08/2003 02:29 PM
Please respond to ORACLE-L
To: Multiple recipients 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 DEFAULT NULL,
p_expire_date IN VARCHAR2 DEFAULT NULL
)
IS
ld_dummy DATE;
BEGIN
ld_dummy := TO_DATE(p_expire_date, 'YYYY-MM-DD');
IF (TO_CHAR(ld_dummy, 'YYYY-MM-DD') = p_expire_date)
THEN
dbms_output.put_line('Modified value: ' || TO_CHAR(ld_dummy,
'YYYY-MM-DD'));
ELSE
dbms_output.put('Err:');
dbms_output.put('p_expire_date="' || p_expire_date || '"');
dbms_output.put_line(',ld_dummy=' || TO_CHAR(ld_dummy,
'YYYY-MM-DD'));
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Something is wrong');
END set_expire_date;
/
SET SERVEROUTPUT ON
EXECUTE set_expire_date(1, 'TEST', '0001- 1-01');
EXECUTE set_expire_date(1, 'TEST', '01-01-01');
EXECUTE set_expire_date(1, 'TEST', '2001-13-01');
EXECUTE set_expire_date(1, 'TEST', '12-2002-01');
EXECUTE set_expire_date(1, 'TEST', '2002-12-01');
EXECUTE set_expire_date(1, 'TEST', ' 01-01-01');
EXECUTE set_expire_date(1, 'TEST', NULL);
EXECUTE set_expire_date(1, 'TEST', '01-JAN-03');
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
[EMAIL PROTECTED] wrote:
> 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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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).
--
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).
