Unexpected Results

A month can contain 28, 29, 30, or 31 days. A year can contain 365 or 366 days. Because of this inconsistency, the following operations can give unexpected results:

• Adding or subtracting a number of months (or calculating a duration in months) with a date that is on the 29th, 30th, or 31st of a month
• Adding or subtracting a number of years (or calculating a duration in years) with a February 29 date.

The following rules are used:

• When months or years are added or subtracted, the day portion remains unchanged if possible. For example, 2000-03-15 + %MONTHS(1) is 2000-04-15.

• If the addition or subtraction would produce a nonexistent date (for example, April 31), the last day of the month is used instead.

• Any month or year operation that changes the day portion is not reversible. For example, 2000-03-31 + %MONTHS(1) is 2000-04-30 changes the day from 31 to 30. You cannot get back the original 2000-03-31 by subtracting one month.

  The operation `2000-03-31

  • %MONTHS(1) - %MONTHS(1)becomes2000-03-30`.

• The duration between two dates is one month if the later date minus one month gives the first date. For example, the duration in months (rounded down) between 2000-03-31 and 2000-04-30 is 0 because 2000-04-30 - %MONTHS(1) is 2000-03-30 (not 2000-03-31).