[sql] T-SQL datetime rounded to nearest minute and nearest hours with using functions

In SQL server 2008, I would like to get datetime column rounded to nearest hour and nearest minute preferably with existing functions in 2008.

For this column value 2007-09-22 15:07:38.850, the output will look like:

2007-09-22 15:08 -- nearest minute
2007-09-22 15    -- nearest hour

Select convert(char(8), DATEADD(MINUTE, DATEDIFF(MINUTE, 0, getdate), 0), 108) as Time

will round down seconds to 00

"Rounded" down as in your example. This will return a varchar value of the date.

DECLARE @date As DateTime2
SET @date = '2007-09-22 15:07:38.850'

SELECT CONVERT(VARCHAR(16), @date, 120) --2007-09-22 15:07
SELECT CONVERT(VARCHAR(13), @date, 120) --2007-09-22 15

I realize this question is ancient and there is an accepted and an alternate answer. I also realize that my answer will only answer half of the question, but for anyone wanting to round to the nearest minute and still have a datetime compatible value using only a single function:

CAST(YourValueHere as smalldatetime);

For hours or seconds, use Jeff Ogata's answer (the accepted answer) above.