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

WooCommerce an WordPress open-source e-commerce plugin

WooCommerce is an WordPress open-source e-commerce plugin. WooCommerce is a free WordPress plugin that have e-commerce functionality to your...