Re: ora-01847 when updating a record...please help

smuckers70_at_hotmail.com (Simon) wrote in message news:<c0475b73.0406012327.434a49cc_at_posting.google.com>...
> not sure if I totally understand your answer...I have looked at the
> data in the table and cannot see any immediate problems. I have done a
> length on the fields
>
> select distinct length(b.free_column9)
> from emp_add_info_values_tab b
> where b.add_info_type = 'B MED INS'
>
> which gives me 10......so they all seem the same?
>
>
Why would you think the length of the text data has anything to do with it?

>
>
> spgangwar_at_yahoo.com (SATYA PAL) wrote in message news:<a7b8a58b.0406011231.4c2a0939_at_posting.google.com>...
> > Hi There,
> >
> > There is no issue in UPDATE statement but the data must incorrect
> > stored in the table. Since you are using VARCHAR2 columna and hence it
> > would allow you to store the first 2 character of date greater than
> > 31.
> >
> > smuckers70_at_hotmail.com (Simon) wrote in message news:<c0475b73.0406010427.57d80809_at_posting.google.com>...
> > > Hello,
> > >
> > > I am trying to update some varchar2 fields, which are storing dates. I
> > > am trying to set a 'to date' with the next minumum from date i.e
> > >
> > > from date | to date
> > > 01/01/1901 01/01/1902
> > > 01/01/1902 01/01/1903
> > > 01/01/1903 etc......
> > >

your sample data has date, month and 4digit year...

> > > I have got a bit lost in creating my update statement, but cannot
> > > understand why I am geting the following error message:-
> > >
> > >
> > > ora-01847 day of month must be between 1 and last day of month.
> > >
> > >
> > > The sql is:-
> > >
> > > update emp_add_info_values_tab a
> > > set free_column10 = (select b.free_column9
> > > from emp_add_info_values_tab b
> > > where a.add_info_type = 'B MED INS'
> > > and a.free_column4 not in ('N','W')
> > > and a.emp_no = b.emp_no
> > > and b.add_info_type = 'B MED INS'
> > > and to_date(b.free_column9,'DD/MM/RR') =

but your conversion format has only the two digit year. []
> > > and to_date(c.free_column9,'DD/MM/RR') >
> > > to_date(a.free_column9,'DD/MM/RR')))
> > >
> > >
> > > Any help would be greatly appreciated...
> > >
> > > Cheers,
> > >
> > > Simon

Simon,

Are you a gambling man? I would bet there is at least one, if not many values in that column like '29/02/1900' or '31/09/1902' or similar bad dates. Want to take the bet?

 BIG HINT: You DO know that 1900 was NOT a leap year, don't you? 8^)

Change your data model and use the DATE type for storing dates and you won't have this kind of insanity. Or at the very least, clean up your data. Text fields like this are notorious for getting bad data somewhere, somehow.

HTH,
  Ed Received on Wed Jun 02 2004 - 22:12:20 CEST