[AccessD] Date Difference As Completed Years-Months-Days

A.D. Tejpal adtp at airtelmail.in
Thu Apr 1 13:39:52 CDT 2010


Gustav,

    Though apparently optimum options amongst various potential alternatives, neither of the two methods, i.e. forward or backward DateAdd() computation can be considered completely perfect. Each represents a degree of compromise inherent in the built-in date arithmetic. 

    This stems from the way VBA handles addition (or subtraction) of given number of months to (or from) a month ending start date. The magic of rounding off the computed finish date to a month end date takes place only if the projected finish date happens to be >= the end date for the finish month.

    For example, adding 1 month to 31-Jan-2010 gets 28-Feb-2010. On the other hand, adding 1 month to 28-Feb-2010 fetches only 28-Mar-2010 (not 31-Mar-2010). Similarly, adding 1 month to 31-Mar-2010 gets 30-Apr-2010, while addition of 1 month to 30-Apr-2010 gets only 30-May-2010 (not 31-May-2010).

    Going backwards, subtracting 1 month from 31-Mar-2010 gets 28-Feb-2010, while doing similar subtraction from 28-Feb-2010 fetches 28-Jan-2010 (not 31-Jan-2010). As an interesting example, if 1 month is subtracted from 31-Mar-2010 and then 1 month is added to the result, we get 28-Mar-2010. Thus for net subtraction/addition of zero month, the starting date value stands reduced by 3. This brings out a weird aspect of a set of DateAdd() operations involving positive and negative values for month argument.

    Considering the fact that time flow is always in forward direction, won't it help simplify matters if it were agreed as a standing practice that elapsed period between any two dates would always be reckoned from lower date to the higher one? Minus qualifier in the result would merely be indicative of the fact that the user has supplied the date arguments in reverse order (inadvertently or by design). That way, the computed difference between any two dates would always be consistent. 

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Thursday, April 01, 2010 00:16
  Subject: Re: [AccessD] Date Difference As Completed Years-Months-Days


  Hi A.D.

  Thanks! 
  Your approach is slightly different than mine and it reveals the issues by these calculations caused by "a month is not a month".
  For example:

    d1=#2008-02-29#
    d2=#2010-01-31#

  YearsMonthsDays(d1, d2) returns:
    1 year(s), 11 month(s), 2 day(s)          

  Fn_GetDateDiffAsYMD(d1, d2) returns:
    1 Year, 11 Months, 2 Days

  Both functions assume that a month's length is the length of February. I can still be in doubt if this is correct; if you had:

    d1=#2008-03-31#
    d2=#2010-01-31#

  you will get a count of 1 year 10 months. 
  Reducing d1 by one month (to #2008-02-29#) will increase the count to 1 year 11 months 0 (not 2) days.
  But this is the way DateAdd works, and as you can't say it is wrong, I find it best - and logical - to accept this behaviour.

  If you accept this, you accept that a count forward of x months may not equal a count backwards of x months.
  Thus, you cannot make a reverse calculation by interchanging the variables, d1 and d2. That is why I created the function to handle both forward and backward periods.
  Taking the same values, now interchanged, as an example:

    d1=#2010-01-31#
    d2=#2008-02-29#

  YearsMonthsDays(d1, d2) returns:
    -1 year(s), -11 month(s), 0 day(s) 

  Fn_GetDateDiffAsYMD(d1, d2) returns:
    (Minus) 1 Year, 11 Months, 2 Days

  For both examples DateDiff returns a count of 23 months or 702 days.

  A third example demonstrates the issue:

    d1=#2012-02-29#
    d2=#2010-01-31#

  YearsMonthsDays(d1, d2) returns:
    -2 year(s), -1 month(s), 0 day(s)

  Fn_GetDateDiffAsYMD(d1, d2) returns:
    (Minus) 2 Years, 1 Month

  And reversing the parameters:

    d1=#2010-01-31#
    d2=#2012-02-29#

  YearsMonthsDays(d1, d2)
    2 year(s), 1 month(s), 0 day(s) 

  Fn_GetDateDiffAsYMD(d1, d2)
    2 Years, 1 Month

  This time both functions return identical results for both directions.
  Your head gets twisted and you understand why banks operate with a count of 30 days for every month.

  /gustav


More information about the AccessD mailing list