Re: Forms 3.0 and dates again...
Date: Mon, 08 Jun 1998 10:02:51 -0700
Message-ID: <357C193B.10EA143A_at_llnl.gov>
Martin Anderson wrote:
>
> How do I force a user to enter in 4-digits for a year without a
> trigger? I was told by Oracle that by using FMFXDD-MON-YYYY the user
> would be forced to enter 4 digits but when I try this NO dates are
> accepted (i.e. 11-OCT-1970 is rejected). Is there a way in Forms v3.0
> to force 4 digit years or not.
>
> Thanks In Advance, Martin
Here is a stored procedure that we use in our Forms 3.0 application. This
stored procedure mimics the RRRR format mask functionality (which is not
available in 3.0). Set the format mask for your field to be 'MM/DD/YYYY' and
place the call to the stored procedure in the ON-VALIDATE trigger for the field
as 'FIX_DATE(:BLOCK.FIELD)'.
Also listed below is a small stored procedure that you can use to test the
FIX_DATE routine.
The RRRR format logic uses the following matrix (taken from Oracle SQL Language Manual):
ENTERED YEAR 0 - 49 50 - 99
CURRENT YEAR 0 - 49 Current Previous Century Century 50 - 99 Next Current Century Century
Hope this helps.
/*************************************************************************/ /* FIX A FOUR-DIGIT YEAR DATE FIELD TO PRESUME CORRECT CENTURY IF */ /* ONLY TWO DIGITS ENTERED AS SHORTHAND. SEE APPENDIX B-19 OF THE *//* ORACLE DEVELOPERS BOOK. ADAPTED TO MIMIC RRRR FORMAT FUNCTIONALITY */ PROCEDURE FIX_DATE (FP_DATE IN OUT DATE) IS
LV_CUR_CENTURY NUMBER(2); LV_CUR_YR NUMBER(2); LV_FP_DATE_YR NUMBER(4); LV_WORK VARCHAR(11);
BEGIN
LV_CUR_CENTURY := TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE,'YYYY'),1,2)); LV_CUR_YR := TO_NUMBER(TO_CHAR(SYSDATE,'YY')); LV_FP_DATE_YR := TO_NUMBER(TO_CHAR(FP_DATE,'YYYY'));IF (LV_FP_DATE_YR < 100) THEN /* ONLY 2-DIGIT YEAR ENTERED */ IF (LV_CUR_YR < 50 AND LV_FP_DATE_YR >= 50) THEN
LV_CUR_CENTURY := LV_CUR_CENTURY - 1;
ELSIF (LV_CUR_YR >= 50 AND LV_FP_DATE_YR < 50) THEN
LV_CUR_CENTURY := LV_CUR_CENTURY + 1;
ELSE
NULL;
END IF;
LV_WORK := TO_CHAR(FP_DATE,'DD-MON-') ||
TO_CHAR(LV_CUR_CENTURY) ||
LPAD(TO_CHAR(LV_FP_DATE_YR),2,'0');
FP_DATE := TO_DATE(LV_WORK,'DD-MON-YYYY');
END IF;
END;
/*************************************************************************/
PROCEDURE TEST_FIX_DATE IS
CUR_CENTURY_DT DATE;
NXT_CENTURY_DT DATE;
BEGIN
CUR_CENTURY_DT := TO_DATE('01/01/0098','MM/DD/YYYY');
NXT_CENTURY_DT := TO_DATE('01/01/0001','MM/DD/YYYY');
DBMS_OUTPUT.PUT_LINE('ORIGINAL CURRENT CENTURY DATE OF 01/01/1998');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(CUR_CENTURY_DT,'MM/DD/YYYY'));
DBMS_OUTPUT.PUT_LINE('ORIGINAL NEXT CENTURY DATE OF 01/01/2001');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(NXT_CENTURY_DT,'MM/DD/YYYY'));
FIX_DATE(CUR_CENTURY_DT);
FIX_DATE(NXT_CENTURY_DT);
DBMS_OUTPUT.PUT_LINE('CORRECTED CURRENT CENTURY DATE OF 01/01/1998');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(CUR_CENTURY_DT,'MM/DD/YYYY'));
DBMS_OUTPUT.PUT_LINE('CORRECTED NEXT CENTURY DATE OF 01/01/2001');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(NXT_CENTURY_DT,'MM/DD/YYYY'));
END;
/*************************************************************************/
--
__________________________________________________
| |
_ | Robert Fernandes - LLNL AIS Department | _
/ ) | | ( \
_( /_ | Phone: (925) 423-1397 | _) )_
(((\ \) |_ Fax: (925) 423-5882 _| (/ /)))
(\\\\ \_/ ) LLNL Email: rfernand_at_llnl.gov ( \_/ ////)
\ / fernandes6_at_llnl.gov \ /
\ _/ \_ /
/ / | Home Email: rfernand_at_ainet.com | \ \
/ / |__________________________________________________| \ \
Received on Mon Jun 08 1998 - 19:02:51 CEST