ORA-01847 day of month must be between one and last day of month

This is really driving me crazy. I run the simple select statement from PL SQL of Oracle database.
field ml504.adj_paid_to_date is a number(8) format.

select to_date(ml504.adj_paid_to_date, 'yyyymmdd')
from ml504

This one runs fine (no errors) meaning the date format is OK.

Now when I add :

select to_date(ml504.adj_paid_to_date, 'yyyymmdd')
from ml504
where extract(month from to_date( ml504.adj_paid_to_date, 'yyyymmdd')) = 1 brings me the above mentioned error for every month I select.

I would appriciate any help on this. Thanks.

u have records in ml504 where adj_paid_to_date has the last 2 digits not between first and last day of that month.

If it was so why to_date function didn't return any errors?
Anyway i changed the stament to :

select to_date(ml504.adj_paid_to_date, 'yyyymmdd')
from ml504
where
substr(to_char(ml504.adj_paid_to_date), 7, 2) = '12' and
extract(month from to_date( ml504.adj_paid_to_date, 'yyyymmdd')) = 7

and still got the same error.

try to loop through a cursor from ml504 and do a dmbs_output before that select (into a variable) and see at which record u get the error.

tatochka,

Did you ever get a resolution to this problem? I am experiencing the same behaviour, but do not know what is causing it.

declare
cursor c is select rownum r, adj_paid_to_date from ml504;
data varchar2(8);
r number(10);
begin
for i in c loop
data:=i.adj_paid_to_date;
r:=i.r;
select extract(month from to_date(i.adj_paid_to_date,'yyyymmdd')) into data from dual;
end loop;
exception
when others then
dbms_output.put_line('row: '||r||' date: '||data);
end;