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

form class help (oop php5)
Hidy Ho Neighbors,

I'm forcing myself to learn oop/classes for php5. It seems like a good id

Casting Decimals in Oracle
This code worked as a query in DB2, but I am not sure what the syntax is for casting decimals in Ora

PHP Thumbnail Creation
Ok so i use this function to create thumbnails:

Code: [Select]function createthumb($name,$fil

How to get the previous months last date....
Here's my wittle problem. I suck at working with dates.

So today is 10/20/2009, i need to fig

insert PHP code into function
Hi,
I'm having problems when trying following:

I have:
Code: [Select]createBar("Do

form variables from database help.
Hi all,

I would like to have a form that gives you options based on the results of an mysql q

Delete HTML file after loading
I have limited experience with php and its been a year or two since I've last used it. I have a sma

how to hide input fields on selection screen using variant attribute
Hello all,

I want to know how to hide input fields on selection screen using variant attr

ctype() validation - allowing illegal characters
Hello,
I use ctype() to filter and validate a user form. However, I am trying to allow certain c

Custom CMS
This is a big custom CMS script I'd like to develop and would like some help atleast figuring out wh