Re: date format in a Trigger

The problem occurs when you store the date/time as dd.mm.yy. You are omitting teh time at this point, and Oracle automatically fills in the time with midnight. Change the way you are storing the date.

"Jochen Pege" <pege_at_kespretlang.de> wrote in message news:9189es$899$1_at_news10.roka.net...
> Sorry,
>
> and tell me how (not who)
>
> Jochen
>
> Jochen Pege <pege_at_kespretlang.de> schrieb in im Newsbeitrag:
> 9188ji$841$1_at_news10.roka.net...
> > Hello everybody,
> >
> > today I Ihave found a problem with the date format in my Before Insert
> > Trigger. In the Trigger I want to update a field called RES_LastChange
 its
> > datatype is date. In the Trigger I use a SQL statement. It is something
 like
> > v_sql:= 'Update Resourcen Set ... RES_LastChange= ''' || sysdate ||
 '''
> > where RES_ID= ' || v_id;
> >
> > Execute Immediate v_sql;
> >
> > After this Update the date is stored like dd.mm.yy.
> >
> > When I try to make a select statement with to_char([Fieldname],
 'dd.mm.yy
> > hh24:mi:ss) I receive
> >
> > dd.mm.yy 00:00:00
> >
> > Now it seems that I'm not able to store the time of updating.
> >
> > It didn't work with
> > v_sql:= 'Update Resourcen Set ... RES_LastChange= ''' ||
> > to_date(sysdate, 'dd.mm.yy hh24:mi:ss') || ''' where RES_ID= ' || v_id;
> >
> > Execute Immediate v_sql;
> > too
> >
> > With
> > :new.RES_LastChange:= sysdate;
> > I have no problems to store the time, but in my case it is not possible
 to
> > store the time in this way.
> >
> > Can anybody solve this problem and tell me who.
> >
> > Best regards
> >
> > Jochen
> >
> >
>
>
Received on Wed Dec 13 2000 - 19:42:48 CET