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

From: Simon <smuckers70_at_hotmail.com>
Date: 1 Jun 2004 05:27:08 -0700
Message-ID: <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 Tue Jun 01 2004 - 14:27:08 CEST