Round datetime to the beginning of the current hour

I would like to round a datetime (should also return a datetime) to the beginning of the hour it’s on, so for example:

2018-09-22 11:31:42.411 -> 2018-09-22 11:00:00.000
2018-09-22 11:08:02.456 -> 2018-09-22 11:00:00.000
2018-09-22 11:52:08.005 -> 2018-09-22 11:00:00.000

Can someone help with this please?

Answer

Assuming the datatype is DATETIME or DATETIME2 you can use this common trick:

SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, yourdate), 0)

DATEDIFF(HOUR, 0, yourdate) will give you the number of hours (and nothing else) between the zeroth date and yourdate. When you add those hours back to the zeroth date you get yourdate “floored” to the hour.