Counting month diff

[atuldalvi]

How to count month difference between 2 dates.

suppose

1st date is 01/05/2010
2nd date is 05/06/2011

then how to count month difference between these two ?

 

[Cringer]

You'll have to define the problems you face more accurately because again this can be interpreted many ways. In your case, is the answer 1? What about 31st May vs 1st June?

 

[atuldalvi]

how come it is 1 because years are different. It should me more than 1. is there any function to calculate this ?

 

[Cringer]

Ah I missed the year being different. There isn't a built in function, no. You will have to write one.

 

[jmac13]

try this not sure if will do what you want but its one ive had for a while

/*----------------------------------------------------------------------
------
    Name : MonthDiff
    Desc : Calculate the difference between 2 dates as months and days.
 
------------------------------------------------------------------------
----*/
  define input  parameter pitStartDate as date       no-undo.
  define input  parameter pitEndDate   as date       no-undo.
  define output parameter poiMonths    as integer    no-undo.
  define output parameter poiDays      as integer    no-undo.  define variable dStartDate        as decimal    no-undo.
  define variable dEndDate          as decimal    no-undo.
  define variable dDiff             as decimal    no-undo.
  define variable tTmpDate          as date       no-undo.
  define variable iDaysInStartMonth as integer    no-undo.
  define variable iDaysInEndMonth   as integer    no-undo.
  /* swap dates if End < Start */
  if pitStartDate > pitEndDate then
    assign 
      tTmpDate     = pitStartDate
      pitStartDate = pitEndDate
      pitEndDate   = tTmpDate.
  /* calculate nr of days in start month */
  assign 
    tTmpDate          = date(month(pitStartDate),15,year(pitStartDate))
+ 20.
    tTmpDate          = tTmpDate - day(tTmpDate).
    iDaysInStartMonth = day(tTmpDate).
  /* calculate start date as decimal */
  dStartDate = year(pitStartDate) * 12 + month(pitStartDate) +
(day(pitStartDate) / iDaysInStartMonth ).
  
  /* calculate nr of days in end month */
  assign 
    tTmpDate        = date(month(pitEndDate),15,year(pitEndDate)) + 20.
    tTmpDate        = tTmpDate - day(tTmpDate).
    iDaysInEndMonth = day(tTmpDate).
  /* calculate end date as decimal */
  dEndDate = year(pitEndDate) * 12 + month(pitEndDate) +
(day(pitEndDate) / iDaysInEndMonth ).
  /* calculate difference in months and days */
  assign 
    dDiff     = (dEndDate - dStartDate)
    poiMonths = truncate(dDiff,0)
    poiDays   = ( dDiff - poiMonths ) * iDaysInStartMonth .
end procedure. /* MonthDiff */

 

[TomBascom]

INTERVAL function

Returns the time interval between two DATE, DATETIME, or DATETIME-TZ values as an INT64 value.

Syntax

INTERVAL (datetime1, datetime2, interval-unit)

datetime1

An expression whose value is a DATE, DATETIME, or DATETIME-TZ.

datetime2

An expression whose value is a DATE, DATETIME, or DATETIME-TZ.

interval-unit

A character constant, or a character expression that evaluates to one of the following time units: ‘years’, ‘months’, ‘weeks’, ‘days’, 'hours’, ‘minutes’, ‘seconds’ or ‘milliseconds’. These values are case insensitive and might be singular.

Notes

This function returns a signed integer value (positive or negative). For example, if datetime1 is less than datetime2, the INTERVAL function returns a negative value.

If datetime1 or datetime2 is a DATE or DATETIME, the time value defaults to midnight and the time zone value defaults to the session's time zone, respectively.

 

[Cringer]

That's good to know Tom - is that a v10 feature?

 

[TomBascom]

Yes.

Yet another reason to get off ancient, obsolete and unsupported releases.

BTW, OE10 is going to be joining the "ancient & obsolete" list fairly soon 10.0 was released in 2003...

 

[Cringer]

Indeed :/ We still have customers on v9 who we can't upgrade. Not entirely sure as to why. It's a right pain. That's not the worst of it though - all our Crystal reports have to be version 8 compliant. That's more obsolete than 9.1E Progress

 

[tamhas]

It is worth asking why ... and hard. 99.99% of the time, there is no good reason.

 

[andyou111]

is that a v10 feature?