ADD_MONTHS function does not return valid date in Oracle

Question

See the results of the below queries:

>> SELECT ADD_MONTHS(TO_DATE('30-MAR-11','DD-MON-RR'),-4) FROM DUAL; 30-NOV-10 >> SELECT ADD_MONTHS(TO_DATE('30-NOV-10','DD-MON-RR'),4) FROM DUAL; 31-MAR-11 

How can I get "30-MAR-11" when adding 4 months to a certain date?

Please, help.

Answer 1

Here is another question about Oracle and Java

It states that

From Oracle link to add_months http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions004.htm

If the date is the last day of the month, or if the result of the month is less than the day component of the day, the result is the last day of the month. Otherwise, the result has the same component of the day as the date.

So, I think you need to manually check the end day and day to change the behavior of the function. Or maybe add days instead of months. (But I did not find the add_day function in ref)

Answer 2

As a workaround, I could use this algorithm:

• Calculate TargetDate1 target date using ADD_MONTHS .

• Alternatively, calculate the TargetDate2 date of TargetDate2 as follows:

  1) apply ADD_MONTHS to the first of the source date dates;
  2) Add the days difference between the source date and the beginning of the same month.

• Choose LEAST between TargetDate1 and TargetDate2 .

Thus, at the end, the target date will contain another component of the day if the day component of the source date is greater than the number of days in the target month. In this case, the end date will be the last day of the corresponding month.

I'm not sure about my Oracle SQL syntaxes, but basically the implementation might look like this:

 SELECT LEAST( ADD_MONTHS(SourceDate, Months), ADD_MONTHS(TRUNC(SourceDate, 'MONTH'), Months) + (SourceDate - TRUNC(SourceDate, 'MONTH')) ) AS TargetDate FROM ( SELECT TO_DATE('30-NOV-10', 'DD-MON-RR') AS SourceDate, 4 AS Months FROM DUAL ) 

---

Below is detailed information on how this method works:

 SourceDate = '30-NOV-10' Months = 4 TargetDate1 = ADD_MONTHS('30-NOV-10', 4) = '31-MAR-11' /* unacceptable */ TargetDate2 = ADD_MONTHS('01-NOV-10', 4) + (30 - 1) = '01-MAR-11' + 29 = '30-MAR-11' /* acceptable */ TargetDate = LEAST('31-MAR-11', '30-MAR-11') = '30-MAR-11' 

And here are some more examples to show different cases:

 SourceDate | Months | TargetDate1 | TargetDate2 | TargetDate -----------+--------+-------------+-------------+----------- 29-NOV-10 | 4 | 29-MAR-11 | 29-MAR-11 | 29-MAR-11 30-MAR-11 | -4 | 30-NOV-10 | 30-NOV-10 | 30-NOV-10 31-MAR-11 | -4 | 30-NOV-10 | 01-DEC-10 | 30-NOV-10 30-NOV-10 | 3 | 28-FEB-11 | 02-MAR-11 | 28-FEB-11 

Answer 3

You can use interval arithmetic to get the desired result.

 SQL> select date '2011-03-30' - interval '4' month 2 from dual; DATE'2011 --------- 30-NOV-10 SQL> ed Wrote file afiedt.buf 1 select date '2010-11-30' + interval '4' month 2* from dual SQL> / DATE'2010 --------- 30-MAR-11 

Remember, however, that there are traps for interval arithmetic if you work with days that do not exist in each month.

 SQL> ed Wrote file afiedt.buf 1 select date '2011-03-31' + interval '1' month 2* from dual SQL> / select date '2011-03-31' + interval '1' month * ERROR at line 1: ORA-01839: date not valid for month specified 

Answer 4

How about something like this:

 SELECT LEAST( ADD_MONTHS(TO_DATE('30-MAR-11','DD-MON-RR'),-4), ADD_MONTHS(TO_DATE('30-MAR-11','DD-MON-RR')-1,-4)+1 ) FROM DUAL ; 

Result: 30-NOV-10

 SELECT LEAST( ADD_MONTHS(TO_DATE('30-NOV-10','DD-MON-RR'),4), ADD_MONTHS(TO_DATE('30-NOV-10','DD-MON-RR')-1,4)+1 ) FROM DUAL ; 

Result: 30-MAR-11

Answer 5

add_months returns the date plus n months.

From November 30 - the last date of the month, adding that 4 months will lead to a date that ends 4 months. This is the expected behavior. If the dates do not change, the workaround is to subtract the day after the new date is returned.

 SQL> SELECT ADD_MONTHS(TO_DATE('30-NOV-10','DD-MON-RR'),4) -1 from dual; ADD_MONTH --------- 30-MAR-11 

Answer 6

 SELECT TO_DATE('30-NOV-10','DD-MON-RR') + ( ADD_MONTHS(TRUNC(TO_DATE('30-NOV-10','DD-MON-RR'),'MM'),4) - TRUNC(TO_DATE('30-NOV-10','DD-MON-RR'),'MM') ) RESULT FROM DUAL; 

This section is paired:

 ADD_MONTHS(TRUNC(TO_DATE('30-NOV-10','DD-MON-RR'),'MM'),4) - TRUNC(TO_DATE('30-NOV-10','DD-MON-RR'),'MM') 

gives you the number of days between the entered date and 4 months later. Thus, adding this number of days to the date you gave, the exact date is given after 4 months.

Link: http://www.dba-oracle.com/t_test_data_date_generation_sql.htm

Answer 7

A simple solution:

 ADD_MONTHS(date - 1, x) + 1 

Answer 8

Here is the trick:

 select add_months(to_date('20160228', 'YYYYMMDD')-1, 1)+1 from dual; 

Enjoy it!

Answer 9

  CREATE OR REPLACE FUNCTION My_Add_Month( STARTDATE DATE, MONTHS_TO_ADD NUMBER ) RETURN DATE IS MY_ADD_MONTH_RESULT DATE; BEGIN SELECT ORACLES_ADD_MONTH_RESULT + NET_DAYS_TO_ADJUST INTO MY_ADD_MONTH_RESULT FROM ( SELECT T.*,CASE WHEN SUBSTRACT_DAYS > ADD_DAYS THEN ADD_DAYS - SUBSTRACT_DAYS ELSE 0 END AS NET_DAYS_TO_ADJUST FROM ( SELECT T.*,EXTRACT(DAY FROM ORACLES_ADD_MONTH_RESULT) AS SUBSTRACT_DAYS FROM ( SELECT ADD_MONTHS(STARTDATE,MONTHS_TO_ADD) AS ORACLES_ADD_MONTH_RESULT,EXTRACT(DAY FROM STARTDATE) AS ADD_DAYS FROM DUAL )T )T )T; RETURN TRUNC(MY_ADD_MONTH_RESULT); END My_Add_Month; / --test & verification of logic & function both SELECT T.*,ORACLES_ADD_MONTH_RESULT + NET_DAYS_TO_ADJUST AS MY_ADD_MONTH_RESULT, My_Add_Month(STARTDATE,MONTHS_TO_ADD) MY_ADD_MONTH_FUNCTION_RESULT FROM ( SELECT T.*,CASE WHEN SUBSTRACT_DAYS > ADD_DAYS THEN ADD_DAYS - SUBSTRACT_DAYS ELSE 0 END AS NET_DAYS_TO_ADJUST FROM ( SELECT T.*,EXTRACT(DAY FROM ORACLES_ADD_MONTH_RESULT) AS SUBSTRACT_DAYS FROM ( SELECT T.*,ADD_MONTHS(STARTDATE,MONTHS_TO_ADD) AS ORACLES_ADD_MONTH_RESULT,EXTRACT(DAY FROM STARTDATE) AS ADD_DAYS FROM ( SELECT TO_DATE('28/02/2014','DD/MM/YYYY') AS STARTDATE, 1 AS MONTHS_TO_ADD FROM DUAL )T )T )T )T; 

Query result

STARTDATE 02/28/2014

MONTHS_TO_ADD 1

ORACLES_ADD_MONTH_RESULT 3/31/2014

ADD_DAYS 28

SUBSTRACT_DAYS 31

NET_DAYS_TO_ADJUST -3

MY_ADD_MONTH_RESULT 3/28/2014

MY_ADD_MONTH_FUNCTION_RESULT 3/28/2014