I am using the following function which takes three parameters date, years to add and months to add and returns a final date.
create or replace function date_add_year_month(p_date in date, p_years in number, p_months in number)
return date
as
v_years number;
v_date date;
begin
v_date := add_months(p_date, p_months);
v_years := p_years + extract(year from v_date);
return to_date(v_years || (extract(month from v_date)) || (extract (day from v_date)), 'YYYYMMDD');
end;
/
now if I run the following test data, I get error of not a valid month.
select date_add_year_month(date '2009-10-28', 0, 10) from dual;
select date_add_year_month(date '2009-10-28', 0, 11) from dual;
select date_add_year_month('2009-Jan-01', 5, 0) from dual;
select date_add_year_month(date '2009-Oct-28', -1, -5) from dual;
but as soon as I start entering Month value 12 or above in function then it starts giving me an output.
select date_add_year_month(date '2009-10-28', 0, 12) from dual;
select date_add_year_month(date '2009-10-28', 0, 13) from dual;
select date_add_year_month(date '2009-Oct-28', 1, 26) from dual;
select date_add_year_month(date '2009-10-28', 0, 36) from dual;
select date_add_year_month(date '2009-10-28', -1, 26) from dual;
When you do extract (month from v_date) the result is a number, which may be a single digit. Adding 10 months to 2009-10-28 gives you 2010-08-28, and extracting the month number from that gives you 8 - and not 08. You're adding zero years in that case, so you end up trying to do:
to_date(v_years || (extract(month from v_date)) || (extract (day from v_date)), 'YYYYMMDD')
which becomes:
to_date(2009 || 8 || 28), 'YYYYMMDD')
which is, after implicit conversion to strings:
to_date('2009828', 'YYYYMMDD')
... and 82 is not a valid month number. When you pass 11 months you get 92. When you pass 12 though you get back to October, the extract gets 10, and you're back to the right number of characters in your string - matching your format model. (If you passed a date in November then 11 months would be OK as that would also still end up in October, and extract would give you 10, again giving you a valid month).
The same sort of thing is happening with all your other date and adjustment combinations. You can get a similar issue with the day number, too.
You could left-pad the extracted numbers to two characters but it would be much simpler to just do:
return add_months(p_date, (p_years * 12) + p_months);
Or don't use your own function at all, as just doing that isn't much more work than calling the function.