I'll admit this right away - I cheated; I used copy and paste to have SSMS work out the answer for me.
I don't feel bad about it either. I know about integer division and I understand all about dateadd and datediff (I even added some tricks to Tibor Karaszi's ultimate guide to the datetime datatypes
), so I think I deserve these points. But feel free to disagree.
The reason I cheated is that the expressions used to set the two datetime variables are way too complex to understand. Especially with their (lack of) formatting. Having to scroll horizontally to see the end of an expression is never a good sign. Same goes for having to count parenthese to find out what belongs together and what doesn't. If one of my staff (which in reality I don't have) turned in this code, I'd send him/her back.
From the explanation, I understand that the focus is not on the lengthy expressions but on the division that follows. So why not, instead of doing al that hard work to set the datetimes to "today" at a fixed time, use a fixed date as well:
SET @startDt = '2010-05-01T10:30:00';
SET @endDt = '2010-05-01T12:00:00';
Then I would have been able to answer the question without cheating, and felt even better about my point.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis