title: Round time to 5 minute nearest SQL Server
body: <p>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.</p>
  
  <pre><code>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) &gt; 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 &lt;= 2
  BEGIN
      SET @val = CAST(CAST(@min as int) - @val as int)
  END
  else
  BEGIN
      IF (@val &lt;&gt; 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)
  </code></pre>
  
  <p>END</p>