Round time to 5 minute nearest SQL Server
17,322
Solution 1
Create this function
create function f_round5min
(
@date datetime
) returns datetime
as
begin -- adding 150 seconds to round off instead of truncating
return dateadd(minute, datediff(minute, '1900-01-01', dateadd(second, 150, @date))/5*5, 0)
end
Use this syntax:
declare @testtable table(date datetime)
insert @testtable values('2013-11-12 12:00'),('2013-11-12 12:01'),
('2013-11-12 12:02'),('2013-11-12 12:02:29'),('2013-11-12 12:02:30'),
('2013-11-12 12:02:31'),('2013-11-12 12:03'),('2013-11-12 12:04'),
('2013-11-12 12:05')
select date, dbo.f_round5min(date) rounded from @testtable
Result:
date rounded
2013-11-12 12:00:00.000 2013-11-12 12:00:00.000
2013-11-12 12:01:00.000 2013-11-12 12:00:00.000
2013-11-12 12:02:00.000 2013-11-12 12:00:00.000
2013-11-12 12:02:29.000 2013-11-12 12:00:00.000
2013-11-12 12:02:30.000 2013-11-12 12:05:00.000
2013-11-12 12:02:31.000 2013-11-12 12:05:00.000
2013-11-12 12:03:00.000 2013-11-12 12:05:00.000
2013-11-12 12:04:00.000 2013-11-12 12:05:00.000
2013-11-12 12:05:00.000 2013-11-12 12:05:00.000
Solution 2
You can calculate the number of minutes since the epoch:
datediff(minute,0,getdate())
Round to 15 minutes by dividing and multiplying by integer 15:
datediff(minute,0,getdate()) / 15 * 15
And add the epoch back:
dateadd(minute, datediff(minute,0,getdate()) / 15 * 15, 0)
For example:
select dateadd(minute, datediff(minute,0,getdate()) / 15 * 15, 0)
-->
2013-11-12 10:45:00.000
![Drako](https://i.stack.imgur.com/k2qy8.jpg?s=256&g=1)
Author by
Drako
Updated on July 21, 2022Comments
-
Drako almost 2 years
i don't know if it can be usefull to somebody but I went crazy looking for a solution and ended up doing it myself. Here is a function that (according to a date passed as parameter), returns the same date and approximate time to the nearest multiple of 5. It is a slow query, so if anyone has a better solution, it is welcome. A greeting.
CREATE FUNCTION [dbo].[RoundTime] (@Time DATETIME) RETURNS DATETIME AS BEGIN DECLARE @min nvarchar(50) DECLARE @val int DECLARE @hour int DECLARE @temp int DECLARE @day datetime DECLARE @date datetime SET @date = CONVERT(DATETIME, @Time, 120) SET @day = (select DATEADD(dd, 0, DATEDIFF(dd, 0, @date))) SET @hour = (select datepart(hour,@date)) SET @min = (select datepart(minute,@date)) IF LEN(@min) > 1 BEGIN SET @val = CAST(substring(@min, 2, 1) as int) END else BEGIN SET @val = CAST(substring(@min, 1, 1) as int) END IF @val <= 2 BEGIN SET @val = CAST(CAST(@min as int) - @val as int) END else BEGIN IF (@val <> 5) BEGIN SET @temp = 5 - CAST(@min%5 as int) SET @val = CAST(CAST(@min as int) + @temp as int) END IF (@val = 60) BEGIN SET @val = 0 SET @hour = @hour + 1 END IF (@hour = 24) BEGIN SET @day = DATEADD(day,1,@day) SET @hour = 0 SET @min = 0 END END RETURN CONVERT(datetime, CAST(DATEPART(YYYY, @day) as nvarchar) + '-' + CAST(DATEPART(MM, @day) as nvarchar) + '-' + CAST(DATEPART(dd, @day) as nvarchar) + ' ' + CAST(@hour as nvarchar) + ':' + CAST(@val as nvarchar), 120)
END
-
Morvael about 8 yearsMight be stating the obvious but if you actually want the time rounded up, just replace dateadd(second,150,@date) with just @date
-
Orin Moyer almost 7 yearsBest non-function answear
-
Grandizer almost 3 yearsIt is the ONLY non-function answer. However, the above function by @t-clausen.dk could be written inline as
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, '1900-01-01', DATEADD(SECOND, 150, MyDateField)) / 5 * 5, 0)