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