ADD_MONTHS function does not return the correct date in Oracle

Question

See the results of 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 some date?

Please help.

Santonin

It can probably be done with some extra (PL/)SQL magic, but how to do it also depends on the desired outcome of '30-OCT-10' plus 4 months. Is that 28/29 FEB or 1/2 MAR? – Brasier

When it comes to adding "months", which have varying number of days, there is no single "correct way" to add them. Oracle provides 2 methods: ADD_MONTHS and interval arithmetic. – Rauscher

Answer 1

There is another question here about Oracle and Java

It states that

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

If date 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. Otherwise, the result has the same day component as date.

So I guess you have to manually check stating day and ending day to change the behaviour of the function. Or maybe by adding days instead of months. (But I didn't find a add_day function in the ref)

Atlantean

You couldn't find any add_days function probably because they deemed it redundant in view of the ability to do a simpler math with + and -. – Vernettaverneuil

@Andriy: if it is the case, how can I write a script to handle all the date in a generic way. not only for this 4 months from november to march. – Santonin

@Mohammed Saligh: Please define this generic way. So far I can assume that you want the day component to be preserved when adding 4 months to 30-NOV-11. But what if you had to subtract 4 months from 31-MAR-11? What should the day part be? – Vernettaverneuil

Answer 2

As a workaround, I might possibly use this algorithm:

1. Calculate the target date TargetDate1 using ADD_MONTHS.

2. Alternatively calculate the target date TargetDate2 like this:

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

3. Select the LEAST between the TargetDate1 and TargetDate2.

So in the end, the target date will contain a different day component if the source date's day component is greater than the number of day in the target month. In this case the target date will be the last day of the corresponding month.

I'm not really sure about my knowledge of Oracle's SQL syntax, 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
)

---

Here is a detailed illustration of how the 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

Vernettaverneuil

Answer 3

You can use interval arithmetic to get the result you want

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

Be aware, however, that there are pitfalls to interval arithmetic if you're working with days that don't exist in every 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

Newish

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

Henrik

Similar to mine but not exactly an improvement – rather a slightly different approach (better than mine – to me, anyway). – Vernettaverneuil

Answer 5

the add_months function returns a date plus n months.

Since 30th November is the last date of the month, adding 4 months will result in a date that's the end of 4 months. This is expected behavior. If the dates are not bound to change, a workaround is to subtract a day after the new date has been returned

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

ADD_MONTH
---------
30-MAR-11

Passivism

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 in paranthesis:

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 number of days between the date you entered and 4 months later. So, adding this number of days to the date you given gives the exact date after 4 months.

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

Pazit

Nice. Just keep in mind, that if you add 3 months to '30-NOV-10' using your method, you will get '02-MAR-11' as the result. – Vernettaverneuil

Answer 7

Simple solution:

ADD_MONTHS(date - 1, x) + 1

Mayan

This looks like a simplification over tekla's suggestion, but seems to be tuned only for cases where the target month has more days than the source month. Won't work as expected when it's the opposite. – Vernettaverneuil

Answer 8

Here is the trick:

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

Enjoy!

Pinsk

Answer 9

We have come to simpler (in our understanding) solution to this problem - take the least day number from original and add_month result dates, as this:

TRUNC(ADD_MONTHS(input_date,1),'MM') + LEAST(TO_CHAR(input_date, 'DD'), TO_CHAR(ADD_MONTHS(input_date,1), 'DD')) - 1 

Some other examples here do not work on every date, below our test results:

WITH DATES as (
    SELECT TO_DATE('2020-01-31', 'YYYY-MM-DD HH24:MI:SS') as input_date,
           '2020-02-29' as expected_date
    FROM dual
    UNION ALL
    SELECT TO_DATE('2020-02-28', 'YYYY-MM-DD HH24:MI:SS'),
           '2020-03-28'
    FROM dual
    UNION ALL
    SELECT TO_DATE('2020-09-30', 'YYYY-MM-DD HH24:MI:SS'),
           '2020-10-30'
    FROM dual
    UNION ALL
    SELECT TO_DATE('2020-09-01', 'YYYY-MM-DD HH24:MI:SS'),
           '2020-10-01'
    FROM dual
    UNION ALL
    SELECT TO_DATE('2019-01-30', 'YYYY-MM-DD HH24:MI:SS'),
           '2019-02-28'
    FROM dual
    UNION ALL
    SELECT TO_DATE('2020-02-29', 'YYYY-MM-DD HH24:MI:SS'),
           '2020-03-29'
    FROM dual
    UNION ALL
    SELECT TO_DATE('2020-09-29', 'YYYY-MM-DD HH24:MI:SS'),
           '2020-10-29'
    FROM dual
    UNION ALL
    SELECT TO_DATE('2020-03-01', 'YYYY-MM-DD HH24:MI:SS'),
            '2020-04-01'
    FROM dual
),
methods as (
SELECT
    input_date,
    expected_date,
    ADD_MONTHS(input_date,1) as standard_way,
    add_months(input_date-1, 1)+1 as wrong_way,
    TO_DATE(LEAST(TO_CHAR(input_date, 'DD'), TO_CHAR(ADD_MONTHS(input_date,1), 'DD')) || '-' || TO_CHAR(ADD_MONTHS(input_date,1), 'MM-YYYY'), 'DD-MM-YYYY') as good_way,
    TRUNC(ADD_MONTHS(input_date,1),'MM') + LEAST(TO_CHAR(input_date, 'DD'), TO_CHAR(ADD_MONTHS(input_date,1), 'DD')) - 1 as better_way
FROM
     DATES
)
SELECT
    input_date,
    expected_date,
    standard_way,
    CASE WHEN TO_CHAR(standard_way,'YYYY-MM-DD') = expected_date THEN 'OK' ELSE 'NOK' END as standard_way_ok,
    wrong_way,
    CASE WHEN TO_CHAR(wrong_way,'YYYY-MM-DD') = expected_date THEN 'OK' ELSE 'NOK' END as wrong_way_ok,
    good_way,
    CASE WHEN TO_CHAR(good_way,'YYYY-MM-DD') = expected_date THEN 'OK' ELSE 'NOK' END as good_way_ok,
    better_way,
   CASE WHEN TO_CHAR(better_way,'YYYY-MM-DD') = expected_date THEN 'OK' ELSE 'NOK' END as better_way_ok
FROM
    methods
;

Barracuda

Answer 10

    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 2/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

Neolamarckism

You have just code in this answer. Explain it a bit for a better answer. – Salina