You CAN use the 'RR' date format from Forms 3.0, with limitations

There was a thread last week on the RR date format in Oracle 7 (where years from 50-99 are in the current century and years from 00-49 are in the next century).

This format can not be used in Forms 3.0 as a Format Mask, but it is possible to use it in a direct SQL call within a PL/SQL block. Here is a short code fragment from a Forms 3.0 on-validate-field trigger that shows how. The field being validated here is the expiration date on a charge card, in MMYY format, which can extend past 1999.

            declare
               work char(6);
               wkdate date;
            begin
            if :expiration is not null then
            work := substr(:expiration,1,2) || '01' || substr(:expiration,3,2);
            select to_date(work,'MMDDRR') into wkdate from dual;
            if last_day(wkdate) < sysdate then
               message('this card is expired');
               bell;
               raise form_trigger_failure;
            end if;
            end if;
            exception
            when value_error then
               message('improper expiration format, not MMYY');
               bell;
               raise form_trigger_failure;
            when others then
               message('improper expiration format, not MMYY');
               bell;
               raise form_trigger_failure;
            end;

As I understand it, The internal code of Forms 3.0, including field validation checks, uses PL/SQL 1.1 instead of PL/SQL 2.X, so you can't use any newer features such as the RR format there. I assume, although I haven't tested it, that changing the system default date format to use RR would produce an error as well.

Assuming leap year isn't a problem, you could probably use a working field of type DATE, then update the database field from the working field by converting it to MMDDYY format with to_char and then back to a date format with to_date using the RR element. (You might have to trap a date of 02-29-00 and handle it separately.)

This isn't as convenient as letting Forms 3.0 do the date checking, but it at least works.

---
Michael Nolan, Sysop for the DBMS RoundTable on GEnie
nolan_at_tssi.com, nolan_at_inetnebr.com
(posted from nolan_at_zeus.unl.edu)