17 replies to this topic

[Anthony Downey]

Hi,

I need to be able to return the number of months that have elapsed between two dates. I'd appreciate sample code anyone might have which will do the trick for me.

The examples I have and their expected results are:
01/01/2001 to 31/01/2001 should return 1
01/01/2001 to 31/01/2002 should return 13
02/01/2001 to 01/02/2002 should return 1
02/01/2001 to 01/01/2002 should return 12

I'm using PRO-IV version 4 rev 514.

Regards,

Anthony

[CSuarezdelReal]

Hi!

There are several ways to implement the solution in ProIV. Do you want a very accurate result? Are you using Oracle?.

Regards,

[Kevin Bruss]

Another question to consider, are the dates fiscal? Some companies
may start a new fiscal year and offset the dates to create smaller
months, for example.

One simplistic approach could be to take the two dates and subtract them
and then divide the result by 30.

#MONTHS = (#A - #B ) / 30

You may even just want the integer of the result of above

#MONTHS = INT((#A - #B ) / 30)

Or, you may want to include rounding and/or decimal precision
instead.

It can get more complicated if you need more accuracy.
For example, one approach would be to build a scratch array for the
number of days for each month of that year, pluck the # of years out
(for each 365 days, add 12mos) and the remaining year
calculate by the array the number of months involved. Of course,
this does not include leap years.

Hope this helps a little..
-Kevin

Edited by Kevin Bruss, 05 November 2003 - 02:49 PM.

[Papa Lazarou]

How about this...

Convert dates to proiv's internal format - numerics... (put your own dates & conversion here)
Make sure neither convert to 0 which means there's an error!

#DATE1 = CDATE('01/01/2001','DD/MM/YYYY')
#DATE2 = CDATE('31/01/2002','DD/MM/YYYY')
IF #DATE1 = 0 OR #DATE2 = 0 THEN EXIT;

Adjust the dates so that Date1 starts with the 1st of the month, and also so that Date2 remains the same number of days on from it...

#DATE2 = #DATE2 - (CONV(CDATE(#DATE1,'DD')) - 1)
#DATE1 = #DATE1 - (CONV(CDATE(#DATE1,'DD')) - 1)

Now add 1 to the end date, because we are including the last day in the calculation...

#DATE2 += 1

Finally, we can find the difference in months...

#MONTHS = (12 * ( CONV(CDATE(#DATE2,'YYYY')) - CONV(CDATE(#DATE1,'YYYY')) ) )
                 + CONV(CDATE(#DATE2,'MM')) - CONV(CDATE(#DATE1,'MM'))

Is that what you were after?

[Anthony Downey]

Thanks for the replies Papa and Kevin.

With regard to the accurary I want a solution that will deliver the results as explained in the initial mail for example 01/01/2001 to 31/01/2001 will give me 1 as the number of months and 02/01/2001 to 01/02/2001 will also return 1 as the number of months. So, yes it needs to be very accurate and aware of leap years.

I'm developing in a PRO-ISAM environment but will be delivering the code to an Oracle environment.

I'll check out your coding suggestions.

Thanks again,

Anthony

[Rick San Soucie]

What number of months should be returned for the following?

30/01/2001 to 28/02/2001
31/01/2001 to 28/02/2001
31/01/2001 to 01/03/2001
02/01/2001 to 28/02/2001
02/01/2001 to 01/03/2001

[Kevin Bruss]

The examples I have and their expected results are:
01/01/2001 to 31/01/2001 should return 1
01/01/2001 to 31/01/2002 should return 13
02/01/2001 to 01/02/2002 should return 1
02/01/2001 to 01/01/2002 should return 12

Anthony, I beleive your results should have been 1,13,13,12 respectively..

Try this logic:

#DIFF_MONTH = CONV(CDATE(#DATE_END,'MM')) -
CONV(CDATE(#DATE_BEG,'MM'))
#DIFF_YEAR = CONV(CDATE(#DATE_END,'YYYY')) -
CONV(CDATE(#DATE_BEG,'YYYY'))
#MONTHS = (#DIFF_MONTH + ( #DIFF_YEAR * 12 ))
IF CONV(CDATE(#DATE_END, 'DD')) <
CONV(CDATE(#DATE_BEG, 'DD')) ELSE
#MONTHS += 1
ENDIF

Values you would set are #DATE_END and #DATE_BEG
Value returned would be #MONTHS

Good luck!
-Kevin

[Kevin Bruss]

For reference,

For Rick's numbers, the above routine I submitted returned:

1, 1, 2, 2, 2

[Rick San Soucie]

I have just been "bitten" by a failure to appreciate the non-US method of writing date formats.

I wrote:

30/01/2001 to 28/02/2001
31/01/2001 to 28/02/2001
31/01/2001 to 01/03/2001
02/01/2001 to 28/02/2001
02/01/2001 to 01/03/2001


I meant to write:

30/01/2001 to 28/02/2001
31/01/2001 to 28/02/2001
31/01/2001 to 01/03/2001
01/02/2001 to 28/02/2001
01/02/2001 to 01/03/2001

I am adding:

30/01/2001 to 01/03/2001
31/01/2001 to 01/03/2001

[Kevin Bruss]

I hear you there.. I have been writing dates like 15-may-2003 for the last 2yrs
now... makes it much easier.

For reference, for Rick's last answers, above routine got:
1, 1, 2, 1, 2, 2, 2

[Rick Young]

Half off-topic

When I am dealing with a UK-owned, US-based company (try that for confusion on a routine basis), I advocated that both sides use YYYYMMDD - amusingly, much like ProIV does anyway in an alpha date.

[Rick San Soucie]

By way of examples, I am asking the question "How do you define a month?" when you say you want the number of months elapsed between two dates.

[Bob Filipiak]

Gents,

Tracking the amount ofelapsed time between two dats has always been a thorn. To throw in a couple more examples:

Jan 15, 2003 to Feb 1, 2003 = ?? months
Jan 15, 2003 to Feb 28, 2003 = ?? months
Jan 30, 2003 to March 1, 2003 = ?? months

So, what do you expect to get as a result of having to use these dates? me thinks your code may get extremely complicated.

Here is my 2 cents

Bob Filipiak

[Glenn Meyers]

Try This:

#FIRST_DATE
#SECOND_DATE
$Y_1 = CDATE(#FIRST_DATE,'YYYY')
$Y_2 = CDATE(#SECOND_DATE,'YYYY')
$M1 = CDATE(#FIRSTDATE,'MON')
$M2 = = CDATE(#FIRSTDATE,'MON')

CASE $M1
  WHEN ='JAN' : #BEGIN = 1,
  WHEN ='FEB' : #BEGIN = 2,
  WHEN ='MAR' : #BEGIN = 3,
  WHEN ='APR' : #BEGIN = 4,
  WHEN ='MAY' : #BEGIN = 5,
  WHEN ='JUN' : #BEGIN = 6,
  WHEN ='JUL' : #BEGIN = 7,
  WHEN ='AUG' : #BEGIN = 8,
  WHEN ='SEP' : #BEGIN = 9,
  WHEN ='OCT' : #BEGIN = 10,
  WHEN ='NOV' : #BEGIN = 11,  
  WHEN ='DEC' : #BEGIN = 12,
ENDCASE 

CASE $M2
  WHEN ='JAN' : #END = 1,
  WHEN ='FEB' : #END = 2,
  WHEN ='MAR' : #END = 3,
  WHEN ='APR' : #END = 4,
  WHEN ='MAY' : #END = 5,
  WHEN ='JUN' : #END = 6,
  WHEN ='JUL' : #END = 7,
  WHEN ='AUG' : #END = 8,
  WHEN ='SEP' : #END = 9,
  WHEN ='OCT' : #END = 10,
  WHEN ='NOV' : #END = 11,  
  WHEN ='DEC' : #END = 12,
ENDCASE  

#TOTAL MONTHS = ((CONV($Y_2) - CONV($Y_1)) * 12) + (CONV($M2) - CONV($M1))

Cheers,
Glenn

[Glenn Meyers]

Try this one (WITHOUT my typos!) :

#FIRST_DATE
#SECOND_DATE
$Y_1 =  CDATE(#FIRST_DATE,'YYYY')
$Y_2 =  CDATE(#SECOND_DATE,'YYYY')
$M1 =  CDATE(#FIRSTDATE,'MON')
$M2 = CDATE(#FIRSTDATE,'MON')
CASE $M1
  WHEN = 'JAN' : #BEGIN =  1
  WHEN = 'FEB' : #BEGIN =  2
  WHEN = 'MAR' : #BEGIN =  3
  WHEN = 'APR' : #BEGIN =  4
  WHEN = 'MAY' : #BEGIN =  5
  WHEN = 'JUN' : #BEGIN =  6
  WHEN = 'JUL' : #BEGIN =  7
  WHEN = 'AUG' : #BEGIN =  8
  WHEN = 'SEP' : #BEGIN =  9
  WHEN = 'OCT' : #BEGIN =  10
  WHEN = 'NOV' : #BEGIN =  11 
  WHEN = 'DEC' : #BEGIN =  12
ENDCASE 

CASE $M2
  WHEN = 'JAN' : #END =  1
  WHEN = 'FEB' : #END =  2
  WHEN = 'MAR' : #END =  3
  WHEN = 'APR' : #END =  4
  WHEN = 'MAY' : #END =  5
  WHEN = 'JUN' : #END =  6
  WHEN = 'JUL' : #END =  7
  WHEN = 'AUG' : #END =  8
  WHEN = 'SEP' : #END =  9
  WHEN = 'OCT' : #END =  10
  WHEN = 'NOV' : #END =  11 
  WHEN = 'DEC' : #END =  12
ENDCASE  

#TOTAL MONTHS =  ((CONV($Y_2) - CONV($Y_1)) * 12) + (#END - #BEGIN)

Cheers!
Glenn