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

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 Tue Jun 01 2004 - 22:31:31 CEST