CODEWITHSUNDEEP
oracle-databaseplsql
haju
haju

Reputation: 1298

plsql - oracle add_months adding additional day

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

Upvotes: 2

Views: 3158

Answers (2)

XING
XING

Reputation: 9886

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.

Upvotes: 0

user5683823
user5683823

Reputation:

ADD_MONTHS does not add a day to the result. Rather, it has this very general feature: if you ADD MONTHS to a date, and that date is the end of the month in that particular month/year, then the result of ADD_MONTHS is the last day of the resulting month.

So, for example, if you add one month to January 31, you always get February 28 (or Feb 29 in a leap year). If you add one month to June 30, you get July 31.

If you add a month to February 28, you get March 31 UNLESS it's in a leap year; in a leap year Feb 28 is not the last day of the month, so if you add a month to it you get March 28.

In all cases, if the result is a day number greater than the number of days in the resulting month, the result is simply the last day of that month.

EDIT: In a comment below this Answer, the OP asks if there is a way to keep the exact same day of the month, with the exception of Feb. 29 (which should become Feb. 28 in a non-leap year).

The answer is YES. As @Xing shows in the other answer, adding interval '99' year will preserve the date, but it may cause trouble when the input date is Feb. 29 and the resulting year is not a leap year. That can be fixed with a preliminary check. It can be done with an IF statement or with a CASE expression. I prefer the latter because it works the same in PL/SQL and in SQL; there is no IF in plain SQL.

toretdate := case when to_char(inputdate, 'mm-dd') = '02-29'
                  then (inputdate + 1) + interval '99' year - 1
                  else inputdate + interval '99' year end

The trick is, when the inputdate is 29 February, push it forward one day to make it March 1, then add 99 years (still March 1), and then subtract one day to make it either February 29 (if it's a leap year) or February 28 (otherwise).

Upvotes: 5

Related Questions