THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Math with Months Is Not Commutative

In other words, if we add a month, then subtract a month, we might not get back to the date we started from. For example:

 SELECT  DATEADD(MONTH, 1, DATEADD(MONTH, -1, '20100330')) ,
DATEADD(MONTH, -1, DATEADD(MONTH, 1, '20100330'))

2010-03-28 00:00:00.000
-03-30 00:00:00.000

 If our code assumes otherwise, it has a bug.

Published Monday, November 29, 2010 5:58 PM by Alexander Kuznetsov



AaronBertrand said:

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

November 29, 2010 6:09 PM

Alexander Kuznetsov said:


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

November 29, 2010 9:13 PM

Geri Reshef said:

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

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



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

1. The last day of February.

2. The 28th day of February.

November 30, 2010 12:54 AM

Frank Kalis said:

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


returns exactly what I would expect.

November 30, 2010 4:12 AM

Luka Ramishvili said:

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.

February 3, 2011 12:13 PM

Alexander Kuznetsov said:

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.

February 3, 2011 9:52 PM
New Comments to this post are disabled

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on and Currently he works as an agile developer.

This Blog


Privacy Statement