Oracle ADD_MONTHS function returns a DATE which is number_of_months later than the input DATE.
Let us understand how to add months to a date by Oracle ADD_MONTH function. We will go through syntax, understand the inputs and return value with some examples in this oracle tutorial.
This page contains (Skip to topic)..
Oracle ADD_MONTHS syntax
ADD_MONTHS ( date, number_of_months )
Oracle ADD_MONTHS returns
The ADD_MONTHS function returns a DATE by adding some months into the input date.
Oracle ADD_MONTHS input parameters and arguments
The ADD_MONTH function takes two arguments.
- The first one is a date which is of DATE datatype or an expression computable to a DATE.
- The second one is an integer. It is number_of_months in this case which specifies the number of months to be added to the date.
Oracle ADD_MONTHS logic
Let us say we have resut_date := ADD_MONTHS(date, n). Here, the date is a DATE and n is an integer.
- If result_date‘s day component exceeds that month’s maximum possible day, then ADD_MONTHS returns the last day of that month.
- If it does not exceed, then the result_date day component will be the same as the date.
Oracle ADD_MONTHS practical example
Here is an illustration of the addition of 4 months to oracle sysdate.
select sysdate "NOW" , add_months(sysdate, 4) "NOW+4 Months" from dual; NOW NOW+4 Mo -------- -------- 01-10-18 01-02-19
As you can see, it keeps the day portion same and adds 4 months to the input date i.e. 01 Oct 2018 + 4 Months becomes O1 Feb 2019.
Oracle ADD_MONTHS tricky example
select to_date('30-11-2018','DD-MM-YYYY') "Nov31" , add_months(to_date('30-11-2018','DD-MM-YYYY'), 3) "Nov31+3 Months" from dual; Nov31 Nov31+3 -------- -------- 30-11-18 28-02-19
We have used Oracle to_date function, Oracle dual table concepts which are already explained before.
This example makes sense. We are trying to add 3 months to Nov 31, 2017. So it becomes Feb 31, 2019. Now Oracle validates the return date i.e. Feb 31, 2019. It finds that this day does not exist in the calendar. Hence, it returns the last day of February month i.e. 28 Feb 2019.
Now had it been a leap year in the result Oracle ADD_MONTHS would have returned 29 Feb 2020.