Monday, November 30, 2015

Round a datetime to the nearest minute (or down to the minute)

Last week I was doing a comparison of 2 datetime fields, but I only wanted to do the comparison on the minute level. So I needed something to round the datetime to the nearest minute or down to the minute.

I found this great answer on SO:

SET @dt = '09-22-2007 15:07:38.850'
SELECT DATEADD(mi, DATEDIFF(mi, 0, @dt), 0)

And if you want to round up or down, you can add 30 seconds to the parameter like this:
SELECT DATEADD(mi, DATEDIFF(mi, 0, DATEADD(s, 30, @dt)), 0)