CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
Mohamed Saligh
Mohamed Saligh

Reputation: 12339

ADD_MONTHS function does not return the correct date in Oracle

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.

Upvotes: 9

Views: 36220

Answers (10)

T.Z.
T.Z.

Reputation: 2162

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
;

Upvotes: 0

Zolar
Zolar

Reputation: 1

Here is the trick:

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

Enjoy!

Upvotes: 0

Tom
Tom

Reputation: 9

Simple solution: 

ADD_MONTHS(date - 1, x) + 1

Upvotes: 0

Shaleen Mehta
Shaleen Mehta

Reputation: 1

    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

Upvotes: -1

tekla
tekla

Reputation: 11

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

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231651

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

Upvotes: 3

Orcun Yucel
Orcun Yucel

Reputation: 94

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

Upvotes: 0

Andriy M
Andriy M

Reputation: 77657

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

Upvotes: 5

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

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

Upvotes: 0

M'vy
M'vy

Reputation: 5774

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)

Upvotes: 9

Related Questions