Date Math Gotcha

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