Date Math Gotcha

From MidrangeWiki
Jump to: navigation, search

We can sometimes get unexpected results, and this applies to several different programming languages and Query Date Math.

A month can contain 28 29 30 or 31 days.

A year can contain 365 or 366 days.

  • Start with date 2005-03-31
  • add one month
  • this gets date 2005-04-30
  • subtract one month
  • this gets date 2005-03-30

In this example we learn that if the math would produce an invalid date 2005-04-31, it lowers the result to the previous valid date, but there is no "memory" of this "rounding".

  • subtract 2005-03-31
  • from 2005-04-30
  • the result is ZERO months
  • because it sees that 30 is less than 31 and not recognize that these are last days of months