Add_Months not Easy to Understand

Posted on 16th Feb 2014 by admin

Oracle is number 1, very fast and very easy. But....
OK, I think but have a problem, only 1 problem I can Found until now. (Even, you can see SQL Sever(date_add), DB2... have the same problem of this function)

I write to you to tell about a problem stay in your Function what I think it is a large Problem. I am very sory if you have knowed it.
OK, now I wil explain about this problem anh the way to fix it or at least, it is the way that you must be explain to your customer how to use it for their work.

It is a problem of Add_Months() function !
Your function work very good with all normal day in the Year but in the the day of the last of the month it return the wrong value, it is very funny. Example, when i work with the day is 30 of the month:

Add_Months('30/11/2008',1)='31/12/2008'
Add_Months('31/12/2008',1)='31/01/2009'
Add_Months('31/01/2008',1)='28/02/2009'
Add_Months('28/02/2009',1)='31/03/2009'
Add_Months('31/03/2009',2)='30/04/2009'
Add_Months('30/04/2009',2)='31/05/2009'
...

After the function, the index of day return is not the same, why ? Because it is knowed that it is the last day of the month. OK, it is no problem if it is the last day of the month. But in case of the day near the last month day, we try:
Add_Months('31/01/2009',1)='28/02/2009'
Add_Months('30/01/2009',1)='28/02/2009'
Add_Months('29/01/2009',1)='28/02/2009'
Add_Months('28/01/2009',1)='28/02/2009'
Add_Months('27/01/2009',1)='27/02/2009'
Add_Months('26/01/2009',1)='26/02/2009'
...

As You see, the 4 day after using the Function return the same day and have no Explain why it is ?
It is the large problem when using this function to calculating automatic Scheduled on the computer. It will not return the same day and my customer will ask why ? Why they can not work with us in the same day of the month? Why the day is not only if the first time they work with us in the day from 28 to 31 of the first Month ?
We can not explain, some time we must using input day by hand to Scheduled time to working with our customer !
Some time we mus us the fix day for our work, example the day is 06, may be:
the first term: 06 and /02/2008 = 06-Feb-2008
the next term: 06 and /03/2008
the next term: 06 and /04/2008
the next term: 06 and /05/2008
the next term: 06 and /06/2008
...
But when we use the day is 29 to 31 there is a large problem. Example for the day is 29:
the first term: 29 and /11/2009
the next term: 29 and /12/2009
the next term: 29 and /01/2009
the next term: 29 and /02/2010 = 29-Feb-2010: Wrong value, not have this day on DateTime
the next term: 29 and /03/2010
...

I think you can fix the problem by using Zezo – Datetime: It is the "zezo term" where you can using Add_Months function to get the all term you want !
The Zezo – Datetime is the day of the previours year, of december and the index of day is the same day you need. Now we can calculate the day of all term by this way:
The first day is 30 of novenber 2009, the next day must be 30 of the another Month (of cause, this Month the function return have at least 30 day Else it is the last day of the Month):

The Zezo – Datetime: 30-Dec-2008:
The first term: 30-11-2009 = Add_Months( 30-Dec-2008, 11): 11= Index of month
The first term: 30-12-2009 = Add_Months( 30-Dec-2008, 12)
The first term: 30-01-2010 = Add_Months( 30-Dec-2008, 13)
The first term: 28-02-2010 = Add_Months( 30-Dec-2008, 14)
The first term: 30-03-2010 = Add_Months( 30-Dec-2008, 15)
The first term: 30-04-2010 = Add_Months( 30-Dec-2008, 16)
The first term: 30-05-2010 = Add_Months( 30-Dec-2008, 17)
The first term: 30-06-2010 = Add_Months( 30-Dec-2008, 18)
The first term: 30-07-2010 = Add_Months( 30-Dec-2008, 19)
The first term: 30-08-2010 = Add_Months( 30-Dec-2008, 20)
The first term: 30-09-2010 = Add_Months( 30-Dec-2008, 21)

You can see:
11 = 11+0 = Index of first Month + 0
12 = 11+1 = Index of first Month + 1
13 = 11+2 = Index of first Month + 2
14 = 11+3 = Index of first Month + 3 and ....
But 0,1,2,3... is the values of the normal Index of all: For, Do while and While Looping when you are coding in your Program.
So this way is you can calculate the next term with the Zezo – Datetime and the Index of the Month of the first term
In your database, if you save Zezo – Datetime (DateStart) and the Index of the first term month (InitTerm) in the table SysDateID:
SysDateID
ID DateStart InitTerm Data ...
001 30-Dec-08 8
...

You can using 1 Update command to calculating all term like:
Update DataTable A
Set TermDate = Add_Months(
(Select DateStart From SysDateID B Where B.ID=A.ID),
TermID + (Select InitTerm From SysDateID C Where C.ID=A.ID) -1)
And the values of all term here:
DataTable
ID TermID TermDate Data ... ...
001 1 30-Aug-09 ...
001 2 30-Sep-09
001 3 30-Oct-09
001 4 30-Nov-09
001 5 30-Dec-09
001 6 30-Jan-09
001 7 28-Feb-10
001 8 30-Mar-10
... ...
I think, it is the very important Example you need add to your Help files, Help online and all Oracle Book and send to your Customer as the explain to using Add_months Function. And I think they will never say Add_Months is the very dificult to use.