Skip to main content

Posts

Showing posts from April, 2014

Rounding time to nearest five minutes - SQL Server

We recently came across a small problem of rounding time to nearest five minutes, so in simple terms 10:41~10:42 becomes 10:40 and 10:43~10:44 becomes 10:45 and so on, and when time is 10:40 or 10:45 no change has to happen in the time result.

We came up with the script below to solve our issue, its a short SQL Server script that took care of things, we would like to share this with you. 


declare @Time datetime select @time = SYSDATETIME() Declare @RoundFactor Int Select @RoundFactor= DATEPART(minute, @Time)% 5 -- Getting Mod of minutes and 5 If @RoundFactor>=3 -- Computing nearest rounding number BEGIN Select @RoundFactor = 5-@RoundFactor END ELSE BEGIN Select @RoundFactor= -DATEPART(minute, @Time)% 5 END