Update: As noted in the comments, this method does not actually round, it truncates the datetime value.
As feature complete as MS SQL Server may be, it really lacks some simple datetime rounding functions. Here is a simple trick to easily round of datetimes at any precision you wish.
We can round off at nearly any precision that SQL Server itself supports, for instance: Minute, Day, Hour, Day, Month, Year and so forth.
In this example I’ll select the original “Created” column from the table tblHits, as well as a rounding of the Created column by the day:
It’ll return the following original / rounded values:
And naturally we can do the same, this time rounded by the hour:
It’ll return the following:
You could of course also do the rounding on the webserver after you’ve selected the database results. Sometimes though, we have to do it at the database level. For instance, if I want to know how many visitors I have per day on my blog:
Now, I know that this is not optimal in terms of performance since we’re doing the DATEADD(DATEDIFF()) trick twice to both select it and group by it, but I’ve kept it this way to follow the KISS principle.