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).
