Re: SQL*Forms and the 21st Century

In article <DB07FJ.AJ1_at_CSUFresno.EDU>, stevec_at_zimmer.CSUFresno.EDU says...
>
>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)

I use in Forms 3.0 a Date field of a field length of 10 and a format mask of "DD.MM.YYYY". This is for european users. I think for US users the mask should be "DD-MON-YYYY". So the user have to enter the correct century. If this is not wanted i wrot a KEY-NXTFLD or POST-CHANGE trigger which actually calls an user exit which adds "19" if the year is given in 2 digits and it is below 50 and so on. In this case it is required to use a CHAR-Field (not on Base Table), and convert every change into a DATE-field (on page 0) with a length of 11 chars (then the default of DD-MON-YYYY as format mask is used). The disadvantage of this is that you could not query the field. Received on Fri Jul 07 1995 - 00:00:00 CEST