Thursday 3 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

No comments:

Post a Comment

How to get code from a published ASP.Net build where we don't have source code?

If you have ever lost or misplaced your source code for an ASP.Net web application, you might wonder if there is a way to recover it from th...