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

1 comment:

  1. I Like It Your Website Content Very Useful Bookmark your website at my browsers thank you keep it please daily update premium blogger templates free download

    ReplyDelete

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...