Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h0922IT21143 for ; Wed, 8 Jan 2003 20:02:18 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h0922Ic21138 for ; Wed, 8 Jan 2003 20:02:18 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id OAA51689; Wed, 8 Jan 2003 14:44:15 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0052ACB9; Wed, 08 Jan 2003 14:29:36 -0800 Message-ID: Date: Wed, 08 Jan 2003 14:29:36 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Vladimir Begun Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Vladimir Begun Subject: Re: PL/SQL Date Format Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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. Jared.Still@radisys.com 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: Vladimir.Begun@oracle.com 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: ListGuru@fatcity.com (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).