Math with Months Is Not Commutative

re: Math with Months Is Not Commutative

AaronBertrand — Mon, 29 Nov 2010 23:09:27 GMT

I take it you just found such a bug. :-)

re: Math with Months Is Not Commutative

Alexander Kuznetsov — Tue, 30 Nov 2010 02:13:13 GMT

Aaron,

I documented a feature, not found a bug. I am not claiming I was the first to document it ;)

re: Math with Months Is Not Commutative

Geri Reshef — Tue, 30 Nov 2010 05:54:20 GMT

..And what do you think about the asociative feature:

Select 2*(3/2),--=2

(2*3)/2;--=3

:-)

I Guess 28/02/2010 can be interpreted as:

1. The last day of February.

2. The 28th day of February.

re: Math with Months Is Not Commutative

Frank Kalis — Tue, 30 Nov 2010 09:12:41 GMT

This happens for all months where the "previous" month has less days than the one you start with. Just try '20101231', for example.

If you subtract a month from 30.03.2010 the result will be 28.02.2010 as there is no 30th day in February and the 28th happens to be the last day in February that year (will return 29th for any leap year). When you add a month again to 28.02.2010, you'll get 28.03.2010, because there is a 28th in every month and this makes it a perfectly valid date and result. It is maybe just not what you would expect if you were looking to get the last day of the month.

On the other hand when you add a month to 30.03.2010 you'll get 30.04.2010, which is another perfectly valid date. Subtracting that month again, gives you the value you started with = 30.03.2010. Makes sense, I think.

So, I guess this is no bug, but a feature. :-)

Btw... doing integer arithmetics this

Select 2*(3/2),--=2

(2*3)/2;--=3

returns exactly what I would expect.

re: Math with Months Is Not Commutative

Luka Ramishvili — Thu, 03 Feb 2011 17:13:13 GMT

It's not a bug, nor a feature, if you do that without computers and T-SQL, you will have the same result.

For example, tell me in 31th of March, what day will be today minus one month, and then that day plus one month.

re: Math with Months Is Not Commutative

Alexander Kuznetsov — Fri, 04 Feb 2011 02:52:06 GMT

Geri, Frank, Luka,

Of course this behavior is not a bug. My point is different: when we assume any other behavior in our code then our code has a bug.

When we need to be more precise, which is often the case in finances, we roll out our own end-of-month and day count conventions.