using add_months function in oracle sql

Oracle ADD_MONTHS Function returns Date by adding months to a specific date. Oracle SQL ADD_MONTHS syntax and examples are below.

---

Oracle ADD_MONTHS Function Syntax

SELECT ADD_MONTHS(date1,m)
 FROM table_name;

In the above Oracle ADD_MONTHS Function Syntax:

• ‘date1’ is the value that will be added with the passed number of months.
• ‘m’ is the number of months that will be added to the ‘date1’ passed.

Oracle ADD_MONTHS Example – Use with SYSDATE

PL/SQL ADD_MONTHS can be used with SQL SYSDATE pseudo column.

Oracle SQL ADD_MONTHS example below returns the Date and time of 4 months ahead.

SELECT ADD_MONTHS(SYSDATE,4)"PL SQL ADD_MONTHS"
FROM DUAL;

Above Oracle ADD_MONTHS example may return something like “2/2/2013 2:18:57 PM”.

The actual value returned will depend upon the SYSDATE value of database.

Note: We have aliased ADD_MONTHS(SYSDATE,4) as PL SQL ADD_MONTHS.

Oracle ADD_MONTHS Example – Using Other Data Types

Oracle ADD_MONTHS Function can be used with other data types.

In the Oracle SQL ADD_MONTHS example below are adding 5 months to a String type parameter.

SELECT 
ADD_MONTHS('01-Jan-2012',5) "PL SQL ADD_MONTHS"
FROM DUAL;

Above Oracle ADD_MONTHS example will return “6/1/2012”.

Note: We have aliased ADD_MONTHS(’01-Jan-2012′,5) as PL SQL ADD_MONTHS.

Oracle ADD_MONTHS Example – Using TO_CHAR

We can use SQL TO_CHAR Function with Oracle PL/SQL ADD_MONTHS Function.

For example, the Oracle ADD_MONTHS query below returns the spelled name of the 5th month from June.

SELECT TO_CHAR(ADD_MONTHS('01-Jun-2012',5),'DD-MONTH-YYYY') "PL SQL ADD_MONTHS"
FROM DUAL;

The above Oracle ADD_MONTHS query returns ’01-NOVEMBER -2012′.

Note: We have aliased TO_CHAR(ADD_MONTHS(’01-Jun-2012′,5),’DD-MONTH-YYYY’) as PL SQL ADD_MONTHS.