SQL Server: Fetch all rows from the current hour

Posted on July 20, 2012 by Brian

I wanted to be able to fetch each and every row from the current hour, but I did not know of a way to set the time portion of a DateTime filter back to the top of the hour without doing some clumsy string manipulation (which lacks any elegance whatsoever). The SQL turned out to be fairly straightforward. I just use the code below to set a variable in any query where I need a “Current Hour” filter. Given there is no string manipulation here, it should translate for use in any locale. This is especially useful in transactional contexts.

Using the DateTime (or SmallDateTime) column on your table, just filter for all rows greater than the result of the filter shown below.  It keeps things quite clean and readable.

This code is specifically for SQL Server, however something similar should work fine in your DB of choice.

Enjoy.

CODE

DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0)

This entry was posted in SQL, SQL Server. Bookmark the permalink.