Re: Oracle Y2K Question

From: Suresh Bhat <oracleguru_at_mailcity.com>
Date: Wed, 24 Mar 1999 21:40:36 GMT
Message-ID: <01be764f$04df7580$a504fa80_at_mndnet>

Mike -

I would really check the actual 4 digit year dates that are being stored in the database by the form.

Do this in SQL*Plus:

alter session set nls_date_format = 'dd-MON-yyyy'; select date1 from table1 where date1 >
to_date('31-dec-1999','dd-mon-yyyy');

OR dates between etc. .....

and see what you get.

I think the form may be putting in 0099 for the year, if the user enters simply 99 for the year.

THE LESSON THAT I LEARNED ABOUT Y2K: You have to control the year entered at the application, sqlload or form level and cannot count on your init.ora NLS_DATE_FORMAT parameter.

Not knowing exactly how you tackled the Y2K problem in your form, here is what we did.

We set date format mask to dd-MON-yyyy in the form; and trapped the error via the following procedure in the on-validate-feild trigger for the date item in the form if they tried to use 2 digit year.

   DEFINE PROCEDURE  

      NAME = check_date_format
      DEFINITION = <<<
      procedure check_date_format(pl_date in date) is
      BEGIN
      /*  **************  Reference from LIBRARY form LIB  ************* 
*/
      /*
         Format mask DD-MON-YYYY on date field does not work properly.
         For example, date entered as 15-OCT-93 changes to 15-OCT-0093
         rather than 15-OCT-1993.  This error is being trapped by this
         procedure check_date_format and on-validate-field trigger or
         key-nxtfld trigger for the date field(s).  It is not an Oracle
         bug. Refer to SQL*Forms Designer's Reference, page B-8.
         Input parameters:
            pl_date= date to be checked
      */
      if substr(to_char(pl_date,'DD-MON-YYYY'),8,2) = '00' then
         message('Date must be entered in DD-MON-YYYY format, Example
                  01-JAN-1993.');
         raise form_trigger_failure;
      end if;
      END;


      >>>

 

   ENDDEFINE PROCEDURE

---

In your OVF trigger you simply need to call this proc:

check_date_format(:block1.date1);

Good luck !!!

Suresh
www.oracleguru.net
oracleguru_at_mailcity.com

Mike Fellows <mfello_at_globalnet.co.uk> wrote in article <7dbd5r$4vu$1_at_newnews.global.net.uk>... Hello there  

I am running Oracle V7.2.3 (Forms 3 and Menu 5) on a HP UX 10.01 platform.  

I have carried out the fixes for Y2K compliancy as recommended by Oracle via the use of ON_VALIDATE triggers to set any YY year datatype to RR and this seems OK.  

My question being when I run a SQL command to look for all records greater than 31-DEC-99 any valid hit is returned correctly, if however I try to look for data BETWEEN say 19-DEC-99 and 20-FEB-02 i dont get the expected response.
The same applies if i look for all records less than eg 20-FEB-00 so in essence when carrying out queries only when I look for records greater than a given date do I get the correct answers.  

Any help would be much appreciated  

Cheers  

Mike

---

Received on Wed Mar 24 1999 - 22:40:36 CET