Re: Date Manipulationan Someone Help - PLEASE ???

In article <luVH9HAuh7yyEw2R_at_bccomp.demon.co.uk>, BC Computing Limited <BCCOMP_at_bccomp.demon.co.uk> wrote:
> I have an input form written in FORMS 4.5, with a date entry field,
> I want the user to be able to enter the date in any format, but to
> store it and display it in DD-MMM-YYYY format.
>
> Yes, it's probably very simple but I've only just finished
> celebrating and I can't quite get my head round it at the moment.

No, it's not very simple, and this will probably make your head hurt even more.

You will need to create your date field on the form as a text item, while your actual date from the database is stored in a hidden item. Use a post-query trigger to display the date in the text item, and in your when-validate-item trigger, copy the valid input date into the hidden date field.

I have done something similar to what you want, so below is most of the code. I included the time element, so you may want to strip out that part of the code. It accepts the date input in a number of formats, and then re-displays the date in MM/DD/YYYY HH:MI:SS format.

Steve Cosner

• When-Validate-Item trigger -------------- Declare Data_Val Varchar2(19) := :Blk.Itm; --Input value from the screen D date; D_Txt varchar2(19); OK_Date Boolean; Begin U04_Check_Date(Data_Val,OK_Date,d,D_Txt,'MDYX'); If not OK_Date then Message(' INVALID DATE. USE FORMAT MM/DD/YYYY HH:MI:SS'); Raise Form_Trigger_Failure; Elsif D_Txt<>Data_Val then copy(D_Txt,:System.Trigger_Item); End If; End;

PROCEDURE U04_Check_Date

• Verifies date and returns date in a standard format to be displayed. --
• Checks D_Txt to determine whether it is a valid date.
• OK_Date is returned True or False.
• D is the date in Oracle Date format
• D_Txt2 is the date formatted according to FmtInd.
• FmtInd values:
• D3YT returns DD-MON-YYYY HH:MI:SS
• MDYX returns MM/DD/YYYY HH:MI:SS, but truncates time if all zeros
• Time is always 24-hour time.
• If century is not specified in input date, it is inferred using the
• sliding-100-Year+10 method

  ---

  (D_Txt in Varchar2, OK_Date out Boolean, D out Date, D_Txt2 out Varchar2, FmtInd in Varchar2) is Len integer := length(D_Txt); Fmt varchar2(30); Fix_Cent boolean := false; Spc integer := instr(D_Txt,' '); Begin If Spc > 6 then Len := Spc-1; End If; If Len = 8 then If Substr(D_Txt,3,1)='/' then fmt := 'MM/DD/YY'; Fix_Cent:=true; Else fmt := 'MMDDYYYY'; End If; Elsif Len = 10 then fmt := 'MM/DD/YYYY'; Elsif Len = 9 then fmt := 'DD-MON-YY'; fix_cent:=true; Elsif Len = 6 then fmt := 'MMDDYY'; fix_cent:=true; Else fmt := 'DD-MON-YYYY'; End If; If spc>6 then fmt := fmt||' HH24:MI:SS'; End If; D := to_date(D_Txt,fmt); If fix_cent then -- Set century here -- If :Parameter.Yr_Num is null then :Parameter.Yr_Num := To_Number(TO_CHAR(SYSDATE,'YYYY')); End If; Declare Yr_Num Integer; CC char(2); Begin Yr_Num := :Parameter.Yr_Num-90; If Substr(D_Txt,Len-1,2)>Substr(To_Char(Yr_Num),3,2) then CC := Substr(To_Char(Yr_Num),1,2); Else CC := Substr(To_Char(Yr_Num+100),1,2); End If; D := To_Date(CC||To_Char(d,'YYMMDDHH24MISS'),'YYYYMMDDHH24MISS'); End; End If; If FmtInd='D3YT' then D_Txt2 := To_Char(d,'DD-MON-YYYY HH24:MI:SS'); Elsif FmtInd='MDYX' then D_Txt2 := To_Char(d,'MM/DD/YYYY HH24:MI:SS'); If Substr(D_Txt2,11,9)=' 00:00:00' then D_Txt2 := Substr(D_Txt2,1,10); End If; End If; OK_Date := TRUE; Exception when others then OK_Date := FALSE; End U04_Check_Date;
  • If you got this far, I'll bet your head really hurts now ;-)