Make the field a date field not a text field. You never store dates in a
text field.
then on insert do to_date(xx,'your format') eg 'mm/dd/yyyy')
Jim
"sxbug" <sxbug.12l42b_at_mail.mcse.ms> wrote in message
news:sxbug.12l42b_at_mail.mcse.ms...
>
> I program a 3 tier application. Now I found users always wrote some
> illegal data into DateTime Field, and Oracle DB all accept them into
> DB(include NULL). But User Application encounter the Unkonow data ,it
> must be halt(Display invilid time ).
> So I think I should make a trigger to check datetime Data.
> I write a trigger,but it is wrong. Please help me .
> Thanks!!
>
>
> ///----------------------
> CREATE OR REPLACE TRIGGER "JAMIS"."T_INSERT_TASK_DATE" BEFORE
> INSERT ON "JAMIS"."TASK"
> FOR EACH ROW
> declare
> myYear String;
> myMonth String;
> myDay String;
> myHour String;
> myMinte String;
> mySec String;
> BEGIN
> myYear = SubStr(:new.DATEOFTASKLAY from 1 for 4);
> myMonth = SubStr(:new.DATEOFTASKLAY form 6 for 2);
> myDay = SubStr(:new.DATEOFTASKLAY form 9 for 2);
> myHour = SubStr(:new.DATEOFTASKLAY form 12 for 2);
> myMinte = SubStr(:new.DATEOFTASKLAY form 15 for 2);
> mySec = SubStr(:new.DATEOFTASKLAY form 18 for 2);
>
> if myYear<'1900' or myYear>'2999' then
> myYear='2004';
> if myMonth<'01' or myMonth>'12' then
> myMonth='12';
> if myDay<'01' or myDay>'31' then
> myDay='01';
>
> if (myHour='') or (myHour<'00' or myHour>'24') then
> myHour='00';
> if (myMinte='') or (myMinte<'00' or myMinte>'60') then
> myMinte='00';
> if (mySec='') or (mySec<'00' or mySec>'60') then
> mySec='00';
>
> :New.DATEOFTASKLAY:=myYear+'-'+myMonth+'-'+myDay+'
> '+myHour+':'+myMinte+':'+mySec;
>
> END;
>
>
>
> --
> sxbug
> ------------------------------------------------------------------------
> Posted via http://www.mcse.ms
> ------------------------------------------------------------------------
> View this thread: http://www.mcse.ms/message447603.html
>
Received on Thu Mar 04 2004 - 01:34:37 CST
