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:

DECLARE @dt DATETIME
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)


Featured Post

Microsoft Fabric Documentation (for Admins)

Maybe this topic is not the most sexiest of them all, but it certainly has the same (or maybe even more) amount of value for YOU! And.. anot...