Add_Months not Easy to Understand

Posted on 16th Feb 2014 07:03 pm 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.

Your Answer:

Login to answer

Other forums

controlling/creating accounts with privileges
hi all,
i'm trying to figure out how i can create/control account with privileges, for instance a

Problems with returning true or false in eval()'d code
Hi guys,

Would appreciate some help with a problem when running eval() on a function that sho

Automatically Detect Phone Model for WAP Jar Deployment
Making a wap site is fairly simple, but I'd like to know if there's a way to make it so that the wap

SCO Unix
I know this might not be the place to ask, but, can anyone tell me if SCO Unix comes with PHP built

Sendmail.php - heading error following check_input
Hi,

I would greatly appreciate some help? I am brand new to PHP and have been searching and e

Login page problems
I developed a website a few months ago and I am now having an issue with logging into it. The place

cURL error
So here is my code... I got it off of here... http://www.youtube.com/watch?v=XcgQUsorF_8
Because

preg_replace question
Hello!!

I hope someone can help me with preg_replace.

I load two tables from an extern

Display thumbnails as square while retaining aspect ratio
I am trying to figure out a way to make an image display as a square, for example 80x80 pixels, when

Need help Updating SQL Server Express DB from c# :(
Please can anyone help with:-
1 How to sructure the strSelect statement.
2 How to instruct