Re: date algorithm
Mike Segel wrote: > > Oh, and I forgot one thing.... > Asumption #2. To be Y2K compliant, the YEARS has to give you the > 4 digit year. (CCYY). Then the algorythm is Y2K compliant. I do > believe that Informix's 4GL does this already, but hey, I don't > want to be accused of doing something that wasn't Y2K compliant. The standard Informix functions are MONTH and YEAR. YEAR always returns the complete year (implicitly 4 digits for years from 1000 onwards, 3 digits for years 100..999, etc). The algorithm assumes that given the dates shown below, the answer on the RHS is correct: DATE1 DATE2 Answer 1999-01-31 1999-02-01 1 1999-01-01 1999-01-31 0 1999-01-01 1999-03-31 2 Also, it doesn't matter whether the day number of DATE1 is later in the same year/month as DATE2 given Mikey's algorithm because the day number is ignored. Note: I am not saying Mikey's algorithm is wrong. Far from it. But you need to be aware that 'the exact number of months between two dates' is not exactly defined. > Mike Segel wrote: > > > Sigh, > > Caveats: I wrote this little gem 4-6 years ago so you may want > > to validate it. Basically its "Use at your own risk...." > > > > Assumptions: DATE 2 is always greater than DATE1! > > > > Let m1 = MONTHS(DATE1) > > Let y1 = YEARS(DATE1) > > > > Let m2 = MONTHS(DATE2) > > Let y2 = YEARS(DATE2) > > Then the number of months between the two dates is as follows: > > results = (y2-y1)*12 - (m1-m2) That's an interesting way of writing it. I think in terms of: (12*y2 + m2) - (12*y1 + m1) but Mikey's expression is algebraically equivalent. Also, despite his protestations to the contrary, I see no reason why it should not work correctly when DATE1 is greater than DATE2 -- the return value will be negative, that's all. > > Its simple, clean and efficient. > > > > -Just a little piece of code from your Uncle Mikey. > > > > Austin Castro wrote: > > > > > I know i might be pushing it, but can anyone help me out with > > > a simple algorithm for a 4gl function which will receive two > > > dates as arguments and calculate the exact number of months > > > between the two dates. thankx a million -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>
Johnathan, For most calculations based on Month, Year, the actual date of the month is ignored. With long term financial instruments, like a 15yr SWAP, you don't do any calculation in days. Most use a day basis of 30/360 which is 30 days a month, 360 days a year. And they will assume the day portion to be the 15th of the month so everything will be nice and neat. For other instruments the daybasis may be 30/365 (30 days a month, and 365 days a year.) It keeps things simpler in doing payment calculations. But you are right, since you could have one date 1/1/99 and the second date as 2/28/99. So is that 1 month or two? For the calculations, its only one month. I guess another calculation you can do to count the number of days and then divide by 30 and drop the remainder. That would give you the "months" but it would wreak havoc on your calculations. OK so I spent way to much time working for financial people. ;-) -Mikey Jonathan Leffler wrote: > Mike Segel wrote: > > > > Oh, and I forgot one thing.... > > Asumption #2. To be Y2K compliant, the YEARS has to give you the > > 4 digit year. (CCYY). Then the algorythm is Y2K compliant. I do > > believe that Informix's 4GL does this already, but hey, I don't > > want to be accused of doing something that wasn't Y2K compliant. > > The standard Informix functions are MONTH and YEAR. > > YEAR always returns the complete year (implicitly 4 digits > for years from 1000 onwards, 3 digits for years 100..999, etc). > > The algorithm assumes that given the dates shown below, the > answer on the RHS is correct: > > DATE1 DATE2 Answer > 1999-01-31 1999-02-01 1 > 1999-01-01 1999-01-31 0 > 1999-01-01 1999-03-31 2 > > Also, it doesn't matter whether the day number of DATE1 is later > in the same year/month as DATE2 given Mikey's algorithm because > the day number is ignored. > > Note: I am not saying Mikey's algorithm is wrong. Far from it. > But you need to be aware that 'the exact number of months between > two dates' is not exactly defined. > > > Mike Segel wrote: > > > > > Sigh, > > > Caveats: I wrote this little gem 4-6 years ago so you may want > > > to validate it. Basically its "Use at your own risk...." > > > > > > Assumptions: DATE 2 is always greater than DATE1! > > > > > > Let m1 = MONTHS(DATE1) > > > Let y1 = YEARS(DATE1) > > > > > > Let m2 = MONTHS(DATE2) > > > Let y2 = YEARS(DATE2) > > > Then the number of months between the two dates is as follows: > > > results = (y2-y1)*12 - (m1-m2) > > That's an interesting way of writing it. I think in terms of: > (12*y2 + m2) - (12*y1 + m1) > but Mikey's expression is algebraically equivalent. Also, despite > his protestations to the contrary, I see no reason why it should not > work correctly when DATE1 is greater than DATE2 -- the return value > will be negative, that's all. > > > > Its simple, clean and efficient. > > > > > > -Just a little piece of code from your Uncle Mikey. > > > > > > Austin Castro wrote: > > > > > > > I know i might be pushing it, but can anyone help me out with > > > > a simple algorithm for a 4gl function which will receive two > > > > dates as arguments and calculate the exact number of months > > > > between the two dates. thankx a million > > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN > #include <disclaimer.h>