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

Strange cookie problem. setcookie dependant on where user was directed from?
Hello,

I have a website that sets a cookie when a user visits the website. The cookie holds a

How to change www in url with a specified name
Hi
I'm trying to change the url http://www.sitename.com/apparels to something like below
http:

help retrieiving results and doing pagination
Having some trouble trying to get the results to show on more than just one page.

What is ha

Simple Scraper... Weird Output
Okay, maybe I just need a Blue Monster and some sleep, but....

I'm scraping a ringtone site

Secure pages Sessions vs. Cookies & session_destroy() help
Im new here and new to PHP, I hope you can help me with some questions.

Im writing my web ap

A href problem under php
Hello...

I tried to explain the issue in an earlier post.. but was not clear enough....

data type in column definition
Hi,
I create a table with column called "Direction of Travel code ".
The travel codes in t

Multiple forms on the same page (safari)
Hello everyone,

I have 3 forms on the same page, that opens in a new window and submits to a

if statements problems
Hi. I'm trying to make a web form, but I kind of hit a dead end trying to figure out why it doesn't

Find current logon time
Hi,
in my sql script i want to query and find out the logon time of the current session.
<