Re: PL/SQL Date Format

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Wed, 08 Jan 2003 14:29:36 -0800
Message-ID: <F001.0052ACB9.20030108142936@fatcity.com>

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

Received on Wed Jan 08 2003 - 16:29:36 CST