Jump to content


Photo
- - - - -

Date Calculation


17 replies to this topic

#16 Kevin Bruss

Kevin Bruss

    Expert

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

Posted 07 November 2003 - 03:00 PM

Glen,

Why not just do this:

#BEGIN = CONV(CDATE(#FIRSTDATE,'MM'))

(I couldn't see any reason for all that case logic..)

Also,
Was your #END var supposed to be using the #FIRSTDATE value?

#17 CSuarezdelReal

CSuarezdelReal

    Advanced

  • Members
  • PipPipPip
  • 91 posts
  • Gender:Male

Posted 07 November 2003 - 03:14 PM

In Oracle, I was thinking on using a dummy table in ProIV. The Deafult Logic or Before Read Logic would be:

$DATE1 = CDATE(#DATE1)
$DATE2 = CDATE(#DATE2)

SQL
SELECT
MONTHS_BETWEEN( TO_DATE(:$DATE1, 'YYYYMMDD'),
TO_DATE(:$DATE2, 'YYYYMMDD'))
FROM DUAL
ENDSQL

You should read the first key of the dummy table as the resulting difference.

That way you let Oracle all the burden of days per month and leap years. Oracle has a lot of functions to deal with different types of data, so why don't use them in ProIV?. Other DBMS should have something similar.

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."

#18 Guest_Guest_Anthony Downey_*

Guest_Guest_Anthony Downey_*
  • Guests

Posted 10 November 2003 - 01:22 PM

I’ve been trying to digest everyone’s input to come up with the ultimate solution. In the end it rests with what the client exactly defines as elapsed months so I’ve gone back to them with a range of test dates for them to determine the elapsed months. Thanks Rick for providing some of these dates.

I have added to Papa’s original code as follows:

PARMS( #FROM, #TO )
IF CDATE((#TO + 1),'MM') # CDATE(#TO,'MM') AND
CONV(CDATE(#FROM,'DD')) > CONV(CDATE(#TO,'DD')) AND
CDATE(#TO,'MM') = '02' THEN
$FROM = CDATE(#FROM,'DD/MM/YY')
$FROM(1,2) = CDATE(#TO,'DD')
#FROM = CDATE($FROM,'DD/MM/YY')
ENDIF
IF CDATE((#FROM + 1),'MM') # CDATE(#FROM,'MM') THEN #FROM += 1;
#DATE2 = #TO - (CONV(CDATE(#FROM,'DD')) - 1)
#DATE1 = #FROM - (CONV(CDATE(#FROM,'DD')) - 1)
#DATE2 += 1
#YEARS = CONV(CDATE(#DATE2,'YYYY')) - CONV(CDATE(#DATE1,'YYYY'))
#MONTHS = #YEARS + (CONV(CDATE(#DATE2,'MM')) - CONV(CDATE(#DATE1,'MM')))
RETURN(#MONTHS)

Thanks Claudio for the suggested solution from Oracle.

When I’ve got my answers I’ll post the results.

Again thanks to everyone who answered my posting.

Anthony



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users