Skip to Main Content
  • Questions
  • ADD_MONTHS Function returning last day of the month

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Arnab.

Asked: March 28, 2020 - 12:52 am UTC

Answered by: Connor McDonald - Last updated: April 02, 2020 - 3:25 am UTC

Category: SQL - Version: Oracle 12c

Viewed 100+ times

You Asked

The question I have is regarding the ADD_MONTHS function. The live sql link for the test case is as below:-
https://livesql.oracle.com/apex/livesql/s/JT1FDGOHXMRWN3LG94GEYLMPJ

If we have a look at this link it has 3 components:-
1. A function that takes an old date and adds a month until the date is greater than the sysdate
2. The function is called with an old date 25/03/1999. The output returns a date 25/04/2020 which is as expected
3. Then the function is called with an old date 28/03/1999. The output returns a date of 31/03/2020. In spite of the fact that the input date was NOT the last date of the month why did the output date become the date of the month? Why does an in-between date 28th Feb go on to dictate that all next monthly dates should be the last date of the month? Or am I missing something?
What would one do if one were to need the date 28/04/2020 instead of 31/03/2020 in the bullet point 3 above?

Thanks
Arnab P

with LiveSQL Test Case:

and we said...

As per the docs

"If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date."

which means that

result := ADD_MONTHS(x,2) ;


is NOT the same as

result := ADD_MONTH(x,1);
result := ADD_MONTH(result,1)


Example

SQL> select add_months(date '2020-01-30',1) from dual;

ADD_MONTH
---------
29-FEB-20

SQL> select add_months(date '2020-02-29',1) from dual;

ADD_MONTH
---------
31-MAR-20

SQL> select add_months(date '2020-01-30',2) from dual;

ADD_MONTH
---------
30-MAR-20


So you can take advantage of MONTHS_BETWEEN for this

SQL> create or replace function fn_get_next_monthly_date(i_in_date date)
  2  return date
  3  is
  4      l_date date;
  5      l_mb int;
  6  begin
  7      l_mb := months_between(sysdate,i_in_date);
  8      l_date := add_months(i_in_date, l_mb);
  9      return l_date;
 10
 11  end fn_get_next_monthly_date;
 12  /

Function created.

SQL>
SQL> select fn_get_next_monthly_date(to_date('25/03/1999', 'dd/mm/yyyy')) from dual;

FN_GET_NE
---------
25-MAR-20

SQL> select fn_get_next_monthly_date(to_date('28/03/1999', 'dd/mm/yyyy')) from dual;

FN_GET_NE
---------
28-MAR-20


and you rated our response

  (1 rating)

Reviews

Next Monthly Anniversary Date

April 01, 2020 - 2:53 pm UTC

Reviewer: Arnab Panja from Kolkata, India

Thanks for the answer.
I have changed the script now to use months_between function. The objective of the script was to do the below

1. Given a date (some time in the back say 28/03/1999)
2. Find out the monthly anniversary date just immediately after the system date.

So after making the changes as you have suggested if I run this using input date (for Point 1) as 28/03/1999 and a system date as 01/04/2020 what I get is 28/03/2020 back which is understood from the script.
Then I need to apply just one more add_months to arrive at the date I need.

I think this method gives me a consistent and correct answer all the time to the objective as I have mentioned above.

Thanks for helping me out.
~ Arnab P

Connor McDonald

Followup  

April 02, 2020 - 3:25 am UTC

Glad we could help

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.