CALCDATE and Leap Year, 29 February. Be aware!

mgn

This post is no question. It is just something I want to share, so you know how to use the DateExpression in the CALCDATE function:

NewDate := CALCDATE(DateExpression [, Date])

You might think that the DateExpression

<1Y+1M>

will give the same result as

<1M+1Y>

, right?
Well, most of the time, but not always!

This week one of my customers reported a bug to me. When creating (monthly) Service Invoices NAV was skipping some Service Contracts. The customer thought it had something to do with the Leap Year, because it happened 4 years ago as well. And because they didn’t fix it back then, but created a workaround, it happened again and I ran into something that resulted in this topic. Note, this bug was due too some (very old) customizations, the standard NAV Service module does not have this bug!

The behavior of how the DateExpression is used by the CALCDATE function is described on MSDN ‘The date expression can be any length. The string is interpreted from left to right with one subexpression at a time. The following rules describe the valid syntax of date expressions’. And it has been noticed a long time ago already by Paul Baxter and perhaps some other blogs I haven't found. But you might not be aware of the consequences when building the DateExpression. At least It wasn’t the first thing that popped up in my mind when confronted with the customer’s bug. So, I spent some time creating examples and thought it my be worth sharing.
Some examples. All date formats in the examples below are in European format, ie. DD-MM-YYYY.
Today is January 1st. So the result of the following functions will be the same:

CALCDATE(<1Y+1M>,01-01-2016) = 01-02-2017

CALCDATE(<1M+1Y>,01-01-2016) = 01-02-2017

But what if the Date is 29, 30 or 31-1-2015, of course February does not have 30 or 31 days. So, it should give the last day in February. And for 2016 this the 29th.

CALCDATE(<1Y+1M>,31-01-2016) = 29-02-2016

CALCDATE(<1M+1Y>,31-01-2016) = 28-02-2016

NAV will calculate <1M+1Y> as follows:
step 1. 31-01-2015+ 1M = 28-02-2015
step 2. 28-02-2015 + 1Y = 28-02-2016

I created a codeunit with some examples. It is giving the following result.




As said, nothing new. But just be aware of this behavior. Happy 2016 leap year!

Alex_Chow

It's working as expected. What's unusual about this behavior?

mgn

Alex_Chow wrote: »

It's working as expected. What's unusual about this behavior?

There is nothing unusual about this. I thought not everyone might be aware of it exactly works and what the consequences are when dealing with February 29.

vaprog

Please note: This post is relevant only regarding the order of terms in the dateformula. The formulae given above do not return the last day of the month in other cases too, as one easily might be tempted to believe.
(e.g. CALCDATE(<1M+1Y>,30-06-2016) = 30-07-2017) not 31st

In order to get the last day of the month add the term +CM

With this in mind, you scarcely will find any real world application where change in order of terms leads to an unexpectantly deviating result, such as the one given above (for a non-programmer's mind, that is). And obviously for some terms, the order matters a lot.

mgn

Vaprog,
Thanks for adding the calculation rules about the last day of the month. I wasn't clear about that in my post.