Re: Trigger Problems with to_date()

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1073959884.628401_at_yasure>...
> clanos wrote:
>
> > Hi there,
> > I'm using php/pear to connect to several databases. One is oracle. When I'm
> > adding a german formated date string to a date field in oracle usually the
> > rdbms returns an error
> >
> > insert into table (datefield) values ('1.1.2004 10:10:10')
> >
> > what I hav to do is using to_date.
> >
> > insert into table (datefield) values (to_date('1.1.2004 10:10:10'))
> >
> > Since I using php-pear I dont want to add extra code to the web site, I
> > started to write an oracle trigger to convert my date string into a oracle
> > readable format.
> >
> > unfortunately my trigger:
> > ----
> > CREATE OR REPLACE TRIGGER DATECONVERT
> > BEFORE
> > INSERT
> > ON SERVERAUSFALL
> > REFERENCING OLD AS OLD NEW AS NEW
> > FOR EACH ROW
> > begin
> > :new.von := to_date(:new.von,'dd/mm/yyyy hh24:mi:ss');
> > :new.bis := to_date(:new.bis,'dd/mm/yyyy hh24:mi:ss');
> >
> > //:new.von = '24.10.2004'
> > //:new.bis = '24.10.2004'
> > end;
> > ----
> >
> > doesn't work.
> > I still get the same error message
> > "[1]: (Error): ORA-01830: date format picture ends before converting entire
> > input string"
> >
> > What am I doing wrong?
> >
> >
> > P.
>
> Your map "dd/mm/yyyy" contains slashes ... your data does not ... it
> contains periods.

Oracle generally doesn't care about that. The problem is that new.von and new.bis are already date fields and they're being used as parameters in the to_date function. The to_date function requires a string variable as it's first parameter.

Ken Denny Received on Tue Jan 13 2004 - 14:54:05 CST