ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Posted on 2006-04-14
I am having trouble running this query.
select cust.customer_id as customer_id, sum(ac.amount)
from customers cust, accounts ac, months m
where cust.CUSTOMER_ID = ac.customer_ID
and ac.month_id = m.month_id
and to_date(m.year_month,'YYYY-MM') < current_date
group by cust.customer_id
This results in an ORA-01841: (full) year must be between -4713 and +9999, and not be 0 error.
The m.year_month is a string field in the format YYYY-MM ex: 2006-04
Any input as to why this query is failing?
I tried it on the months table, which has the year_month field and in there it works fine.
In other words, this query works fine
from months m
where to_date(m.year_month,'YYYY-MM') < current_date
There are NO null or empty values in the result set. I tried that by removing the filter and displaying the to_date(m.year_month,'YYYY-MM') field to see if there are any invalid values. There were no invalid values.
I am totally lost. Please help