PL/SQL Date Format

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Tue, 07 Jan 2003 15:20:25 -0800
Message-ID: <F001.005295B0.20030107152025@fatcity.com>

Okay, PL/SQL programmers, a lowly dba is in need of your assistance. If you will show pity on my poor self, who does not deserve even the mearest consideration, I will be greatly indebted...  

I have a proc that needs to process a date field. The users want to enter it in a specific format (YYYY-MM-DD) that is not the same as the system format (DD-MON-YY). If they do not enter the correct format, I need to raise an exception. The parameter was set as DATE, but it would not allow me to enter the requested format, so I changed it to VARCHAR2. When it was date, it would not accept the requested format. When it is varchar2, PL/SQL does an implict conversion of the date. Unfortunately, it is an incomplete conversion and the date is not correct (see example below).    

SQL> execute qa_subs.set_expire_date(1,'TEST','01-JAN-01'); This is set_expire_date
Expire date is 0001-01-01  

SQL> execute qa_subs.set_expire_date(1,'TEST','2001-01-01'); This is set_expire_date
Expire date is 2001-01-01  

So I added a substr to extract the date and try to convert it to numbers. Very unelegant...  

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
BEGIN
   dbms_output.enable(10000);
   dbms_output.put_line('This is set_expire_date');

   v_expire_year := substr(p_expire_date, 1, 4);
   v_expire_month := substr(p_expire_date, 6,2);
   v_expire_day := substr(p_expire_date, 9,2);
   v_expire_date := to_date(p_expire_date, 'YYYY-MM-DD');

   dbms_output.put_line('Expire date is '||to_char(v_expire_date,
'YYYY-MM-DD'));
    EXCEPTION
   WHEN INVALID_NUMBER THEN

      dbms_output.put_line('Invalid Date format');
      dbms_output.put_line('Format must be YYYY-MM-DD ('||to_char(sysdate,

'YYYY-MM-DD')||')');

   WHEN VALUE_ERROR THEN

      dbms_output.put_line('Invalid Date format');
      dbms_output.put_line('Format must be YYYY-MM-DD ('||to_char(sysdate,

'YYYY-MM-DD')||')');

END set_expire_date;  

Is there a method (other than altering the session before calling the proc) to force an input value to be in a certain format? I've checked my docs and online and I'm drawing a blank.  

With Humble regards,  

Dan Fink

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Fink, Dan
  INET: Dan.Fink_at_mdx.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 Tue Jan 07 2003 - 17:20:25 CST