Jump to content


Photo
- - - - -

Date Calculation


17 replies to this topic

#1 Anthony Downey

Anthony Downey

    Member

  • Members
  • PipPip
  • 15 posts
  • Gender:Male
  • Location:Co. Monaghan, Ireland

Posted 05 November 2003 - 12:18 PM

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

#2 CSuarezdelReal

CSuarezdelReal

    Advanced

  • Members
  • PipPipPip
  • 91 posts
  • Gender:Male

Posted 05 November 2003 - 01:31 PM

Hi!

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

Regards,
Claudio Suárez del Real
"It is not the strongest of the species that survive, nor the most intelligent, but the ones most responsive to change."

#3 Kevin Bruss

Kevin Bruss

    Expert

  • Members
  • PipPipPipPip
  • 113 posts
  • Gender:Male
  • Location:Oklahoma City, OK

Posted 05 November 2003 - 02:48 PM

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.


#4 Papa Lazarou

Papa Lazarou

    Member

  • Members
  • PipPip
  • 38 posts
  • Gender:Male
  • Location:Royston Vasey
  • Interests:Collecting wives, pegs.

Posted 06 November 2003 - 02:12 AM

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?
You're my wife now, Dave!

#5 Anthony Downey

Anthony Downey

    Member

  • Members
  • PipPip
  • 15 posts
  • Gender:Male
  • Location:Co. Monaghan, Ireland

Posted 06 November 2003 - 09:08 AM

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

#6 Rick San Soucie

Rick San Soucie

    Member

  • Members
  • PipPip
  • 36 posts
  • Gender:Male
  • Location:Dallas, United States

Posted 06 November 2003 - 02:35 PM

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

#7 Kevin Bruss

Kevin Bruss

    Expert

  • Members
  • PipPipPipPip
  • 113 posts
  • Gender:Male
  • Location:Oklahoma City, OK

Posted 06 November 2003 - 04:00 PM

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

#8 Kevin Bruss

Kevin Bruss

    Expert

  • Members
  • PipPipPipPip
  • 113 posts
  • Gender:Male
  • Location:Oklahoma City, OK

Posted 06 November 2003 - 04:07 PM

For reference,

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

1, 1, 2, 2, 2

#9 Rick San Soucie

Rick San Soucie

    Member

  • Members
  • PipPip
  • 36 posts
  • Gender:Male
  • Location:Dallas, United States

Posted 06 November 2003 - 04:11 PM

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

#10 Kevin Bruss

Kevin Bruss

    Expert

  • Members
  • PipPipPipPip
  • 113 posts
  • Gender:Male
  • Location:Oklahoma City, OK

Posted 06 November 2003 - 04:24 PM

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

#11 Rick Young

Rick Young

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 265 posts
  • Gender:Male
  • Location:Guelph, Canada

Posted 06 November 2003 - 04:29 PM

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.

#12 Rick San Soucie

Rick San Soucie

    Member

  • Members
  • PipPip
  • 36 posts
  • Gender:Male
  • Location:Dallas, United States

Posted 06 November 2003 - 04:47 PM

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.

#13 Bob Filipiak

Bob Filipiak

    Expert

  • Members
  • PipPipPipPip
  • 133 posts
  • Gender:Male

Posted 06 November 2003 - 06:13 PM

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

#14 Glenn Meyers

Glenn Meyers

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 221 posts
  • Gender:Male
  • Location:St. Louis, MO, United States
  • Interests:I also raise African Gray Parrots and build hot rod automobiles.

Posted 07 November 2003 - 02:19 PM

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

#15 Glenn Meyers

Glenn Meyers

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 221 posts
  • Gender:Male
  • Location:St. Louis, MO, United States
  • Interests:I also raise African Gray Parrots and build hot rod automobiles.

Posted 07 November 2003 - 02:29 PM

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



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users