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 ;-)
Received on Fri Jan 03 1997 - 00:00:00 CST
