I both liked and disliked this question.
The reason I like it, is that it points out a very common misconception when querying for intervals - I have lost track of how often I have seen people try to query for a one-day interval using some variation of the code in this question, instead of the correct way as demonstrated in the answer.
The reasons for not liking it are less significant, but there are more so they add up.
1) The way the answers were presented made it very hard to see exactly what they were. Each answer had two or three datetimes, often not in chronological order - the numbers started dancing before my eyes and I almost missed that one of the answers had a day number 30 instead of 31. I think it would have been easier to present one datetime value per answer options, arranged chronologically. After all, the QotD is about testing SQL Server skills, not skills in reading lots on similar looking numbers...
2) The "correct" answer lists four datetime values. There is no direct correspondence to any of the given answer options. I guess that the "correct" answer is the UNION of two or more of the given options, but after staring at these numbers to choose my answer, I frankly couldn't be bothered to repeat the exercise.
3) The "correct" answer is not correct at all. There is no way that 2009-01-30 00:00:00.000 can ever be returned by this query.
4) I saw only one answer that I thought to be completely correct. But the QotD permitted multiple answers, and when I submitted my own questions, I found no way to mark a question as "tick all that apply" without marking at least two answers as correct, so I assumed there had to be a second "correct" answer. The only one that wasn't obviously wrong was the first one, figuring that the author himself had accidentally missed that 2009-01-31 12:59:59:128 woukd be rounded to ~.127. All other answers were more wrong (in my eyes).
5) It's really a shame that the explanation in the answer focuses entirely on the ending numbers that can be returned (maybe that made Steve think the answer including Jan 30 should have been marked as correct as well?). A very important point here, maybe more important than the possible ending number, is that the datetime used in the query will also be rounded to one of those ending numbers - and in this case, the rounding will be UP
, not down (rounding to the nearest value), so that feb 1st, midnight exactly, will be included in the results. This is what has taken many people br surprise, and should in my eyes have been the biggest takeaway of this question. Not whether SQL Server can return a datetime with a time part of 12:37:53.128.
6) Technically, all answers were wrong. SQL Server uses a period to seperate milliseconds from seconds, not a colon as shown in the answers.
All in all, I applaud the author for the idea of writing a QotD about the way datetimes are rounded and how a filter for a time interval should or shouldn't be constructed, but I am very disappointed that the many small errors made this a missed opportunity.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis