Open In App

PLSQL | ADD_MONTHS Function

Improve
Improve
Like Article
Like
Save
Share
Report

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.11.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.


Last Updated : 25 Sep, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads