PL/SQL Date Format

[Fink, Dan]

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