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

RSS feed - FeedList for WordPress
Hi,

I'm trying to alter the FeedList plugin for WordPress so that I can click on the title of

Not connecting to DB using ruby, error:env.c:257:in oci8lib.so: ORA-12154:
Hi All,

I am trying to connect to the database server from the client server using ruby and f

Intrastat Report Config in ECC 6.0 - goods Movement in EU countries
Colleagues, need ur help to understand the changes require in ECC 6 standard Report configuration se

Securing a user input - need some confirmation
Hello All,

I am in the process of recoding a large proportion of an e-commerce site, one of t

update sql when refresh - php
hi
I have made a table (attachement)

the users can update the sql database using + or x bu

email form (cannot find the problem)
Hello there. I've been having trouble with an email form. Can't find the problem really. I've tested

What are causes of a connection-timeout with fopen()?
Hello! Here is the situation: The server I host my website on just upgraded it's PHP build from 4.4.

HTAccess auto log in?
Hi all.

Too busy driving celebs about lately so I hope someone here can help me out.

I

Convert .fdf to .pdf
I currently have a web form that uploads the form data to an .fdf file and emails it.

However

Help with forum quoting?
Hi im working on a forum and I have alomost finished it but i want a user quote system like twitter