Round time to 5 minute nearest SQL Server
        Posted  
        
            by 
                Drako
            
        on Stack Overflow
        
        See other posts from Stack Overflow
        
            or by Drako
        
        
        
        Published on 2013-11-12T09:31:57Z
        Indexed on 
            2013/11/12
            9:54 UTC
        
        
        Read the original article
        Hit count: 288
        
sql
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
© Stack Overflow or respective owner