plsql - oracle add_months adding additional day

Question

Oracle: 12c

Description: Trying to add 99 years to a date using add_months, which works however it adds an additional day to the return date value. How should i properly add years to a date?

declare 
  toRetDate DATE;
  inputDate DATE;
  numYears number;
begin

  numYears := 99;
  inputDate := TO_DATE('28-FEB-85', 'DD-Mon-YY' );
  toRetDate := add_months(inputDate, numYears*12);
  DBMS_OUTPUT.put_line(toRetDate);
end;

Output:29-FEB-84

Answer 1

You can use this way:

declare 
  toRetDate DATE;
  inputDate DATE;
  numYears number;
begin

  numYears := 99;
  inputDate := TO_DATE('28-FEB-1985', 'DD-Mon-YYYY' );
  toRetDate := inputDate + INTERVAL '99' YEAR; --add_months(inputDate, numYears*12);
  DBMS_OUTPUT.put_line(to_date(toRetDate,'dd-mon-yyyy'));
end;

Although it's worth noting that the arithmetics with intervals is limited (if not broken) because it simply "increments" the month/year value of the date value. That can lead to invalid dates (e.g. from January to February).

EDIT: In General Add_months generally adds 30/31(depending on months) to the date on which its applied. The case is slight different for monthends. If add_months is done for month of FEBRUARY, it will add (28/29) days depending its a leap year or not. If your requirement is to simple add 99 years without checking if the resultant date is a valiad or not, then you can use INTERVAL, but if you really wanted to check if the resultant date should be very well evaluted and correct date then you must your ADD_MONTHS. It does not make any sense to first use INTERVAL then check if the resultant date(using any logic) is a valid date or not. Oracle has already provided solution for such scenarios.