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

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?

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......
> >
> > 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') =
> > (select min(to_date(c.free_column9,'DD/MM/RR')) from
> > emp_add_info_values_tab c
> > where c.add_info_type = 'B MED INS'
> > and c.free_column4 not in ('N','W')
> > and a.emp_no = c.emp_no
> > and to_date(c.free_column9,'DD/MM/RR') >
> > to_date(a.free_column9,'DD/MM/RR')))
> > where exists(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') =
> > (select min(to_date(c.free_column9,'DD/MM/RR')) from
> > emp_add_info_values_tab c
> > where c.add_info_type = 'B MED INS'
> > and c.free_column4 not in ('N','W')
> > and a.emp_no = c.emp_no
> > 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
Received on Wed Jun 02 2004 - 09:27:55 CEST