PLSQL | ADD_MONTHS Function

The PLSQL ADD_MONTHS function is used to return a date with a specified number of months added to it. The ADD_MONTHS function accepts two parameters which are the initial date and the number of months to be added to it.
The ADD_MONTHS function returns a value of the date data type.

The date argument can be a datetime value or any value that can be implicitly converted to DATE. The integer argument to be added can be an integer or any value that can be implicitly converted to an integer. The return type is always DATE, regardless of the data type of date. If the date specified in the argument 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.

Syntax:

ADD_MONTHS( init_date, add_months )

Parameters Used

  1. init_date – It is used to specify the initial date.
  2. add_months – It is used to specify the number of months to be added to the initial date.

Return Value:
The ADD_MONTHS function returns a value of the date data type.



Supported Versions of Oracle/PLSQL:

  1. Oracle 12c
  2. Oracle 11g
  3. Oracle 10g
  4. Oracle 9i
  5. Oracle 8i

Example-1: Using positive numeric value in the add_months argument of the ADD_MONTHS function.

DECLARE 
   Test_Date date := '01-Aug-18';
   Add_Month number := 3;
   
BEGIN 
   dbms_output.put_line(ADD_MONTHS(Test_Date, Add_Month)); 
   
END; 

Output:

01.12.18 

Example-2: Using negative numeric value in the add_months argument of the ADD_MONTHS function.

DECLARE 
   Test_Date date := '01-Aug-18';
   Add_Month number := -3;
   
BEGIN 
   dbms_output.put_line(ADD_MONTHS(Test_Date, Add_Month)); 
   
END; 

Output:

01.05.03 

Example-3: Using negative numeric value in the add_months argument of the ADD_MONTHS function.

DECLARE 
   Test_Date date := '31-Aug-18';
   Add_Month number := -4;
   
BEGIN 
   dbms_output.put_line(ADD_MONTHS(Test_Date, Add_Month)); 
   
END; 

Output:

30.04.18 

Since April has 30 days, the ADD_MONTHS returns 30.04.18 as the last day.

Example-4: Using a positive numeric value in the add_months argument of the ADD_MONTHS function.

DECLARE 
   Test_Date date := '31-Aug-18';
   Add_Month number := 3;
   
BEGIN 
   dbms_output.put_line(ADD_MONTHS(Test_Date, Add_Month)); 
   
END; 

Output:

30.11.18 

Since November has 30 days, the ADD_MONTHS returns 30.11.18 as the last day.

Example-5: Using select query with the ADD_MONTHS function.

SELECT
  ADD_MONTHS( DATE '2016-02-29', 1 )
FROM
  dual; 

Output:

31-MAR-16 

Advantages:
The integer argument to be added can be an integer or any value that can be implicitly converted to an integer.

My Personal Notes arrow_drop_up


If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.