Saturday, March 3, 2012

Understanding ADD_MONTHS function

Do you know everything about ADD_FUNCTION in oracle? Let us have a reality check. Have a look at the below query and predict the output,

SELECT ADD_MONTHS('31-JAN-2012',1),
                ADD_MONTHS('30-JAN-2012',1),
                ADD_MONTHS('30-MAR-2012',1),
                ADD_MONTHS('30-APR-2012',-1) ,
                ADD_MONTHS('29-FEB-2012',1)
   FROM  DUAL;

Execute the above query and match your predictions with actual results. If you have got everything correct then I suggest not to waste your time by reading further ;). If you have not got everything right then its fair to say that you need to understand ADD_MONTHS function better.

My suggestion to those who think there must be something wrong with ADD_MONTHS function is that read the below extract from oracle manual about ADD_MONTHS function,

"ADD_MONTHS returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d."

The above statement clearly defines two boundary conditions. They are

1. If input date d is the last day of the month then ADD_MONTHS function will always return last day of the resulting month.
For instance, ADD_MONTHS('29-FEB-2012',1) will return '31-Mar-2012' rather than '29-Mar-2012'

2. If the resultant month has fewer days than input day then the result will be last day of the month
For instance ADD_MONTHS('30-JAN-2012',1) will return '29-Feb-2012'

The above two are very fair boundary conditions in order to ensure that ADD_MONTHS works fine for all dates with out any issue.

Every one will be happy with this behavior until you have a requirement to shift months (add or subtract) with out changing the date like below,

         fn('30-Nov-2012',1) = '30-Dec-2012'

Unfortunately ADD_MONTHS does not support this. But there is a work around. This can be achieved by using Interval arithmetic. Unlike ADD_MONTHS function INTERVAL '1' MONTH just shifts the month from November to December but keep the date as 30th.

 SELECT DATE '30-NOV-2012' + INTERVAL '1' MONTH
    FROM DUAL;

But this solution comes in with a pitfall. The interval arithmetic will fail with invalid date exception when the resultant months has fewer days than you input date. For instance the below query will fail since the month of February does not have 30 days.

SELECT DATE '30-NOV-2012' + INTERVAL '3' MONTH     
   FROM DUAL;

No comments: