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

user_219628 0
Viewed 171768

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
sql-server-2008 tsql sql-server sql
4 Answers

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.

user_5280610

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

will round down seconds to 00

user_75465490
Related