Re: SQL*Forms and the 21st Century

From: Steve Cosner <stevec_at_zimmer.CSUFresno.EDU>
Date: 1995/06/30
Message-ID: <DB07FJ.AJ1_at_CSUFresno.EDU>#1/1

In article <3svdmp$nek_at_mccoy.ici.net> jack_at_ici.net (Jack Bell) writes:
>Greetings Oracle users!
>
>As the 21st Century approaches a problem has surfaced with some
>SQL*Forms 3.0 data input forms I created. It involves the
>evaluation of dates which occur beyond 31-DEC-1999. The forms
>use the default DD-MON-YY format for date display. Users
>noticed that dates entered with 00 for the year were always
>evaluated as occuring in 1900. To correct this I made an
>on-validate-field trigger as follows:
>
> if :date_field < '01-JAN-50' then
> :date_field := add_months(:date_field,1200);
> end if;
>
>It did not solve the problem, the dates were still evaluated in
>the wrong century! I seem to remember reading that fields were
>treated as character data until convertion at insert time. I
>suspect that this is the reason the first 2 digits of the year
>never get corrected by the trigger, since they are not included
>in the form's field. I realize that there are alternative
>solutions to this such as on-insert/on-update triggers, but ....
>
>
>Has anyone got a simple solution which keeps the current field
>format?
>
>Jack
>
There are *NO* simple solutions to date/century problems.

First, we do something similar to your method, and the form seems to remember the correct century set by the on-validate-field trigger process. Are you sure the add_months procedure works right? Is add_months your own procedure? I can't find it in any documentation. (I use Forms 4.5, so this may be different.)

Second, your condition will not work after 1999:

   if :date_field < '01-JAN-50' then...
(In 2000 and later, your date literal will be translated to 01-JAN-2050 !!)

   You should really do this:
    if :date_field < TO_DATE('01-JAN-1950','DD-MON-YYYY') then...

But... there is still a problem. What if in Jan, 2000, your user enters a date of 15-DEC-99? Now your form will translate the date to 15-DEC-2099. Change the date on your client PC and see for yourself.

You really need a function like we use:

  FUNCTION CENTURYF (YY CHAR) RETURN CHAR IS     YR_YYYY NUMBER(4) := TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 90;

• We actually store sysdate in the form, so it only
• needs to request it from the server once. BEGIN IF SUBSTR(YY,1,2) > SUBSTR(TO_CHAR(YR_YYYY),3,2) THEN RETURN SUBSTR(TO_CHAR(YR_YYYY),1,2); ELSE RETURN SUBSTR(TO_CHAR(YR_YYYY + 100),1,2); END IF; END;

now, in your trigger, you can do this:
  :date_field := to_date(centuryf(to_char(:date_field,'YY'))

                                ||to_char(:date_field,  'YYMMDD'),
                                                      'YYYYMMDD');

This function and trigger will always force a date variable to contain a century within the range of 89 years before and 10 years after the current year. For example, in 1995, if a user enters 01-JAN-06, the form translates the year to 1906; 31-DEC-05 translates to 2005; 01-JAN-99 translates to 1999. In 1996, 01-JAN-06 translates to 2006. The process continues to work forever.

Your alternative to using something complex like this is to force users to enter 4-digit years, which they will probably hate.

I know what you are saying: you hate it! But, if anyone can find a better solution to processing dates, then please... let us all know!

Steve Cosner (stevec_at_csufresno.edu) Received on Fri Jun 30 1995 - 00:00:00 CEST