T-Sql: Round to Nearest 15 Minute Interval

T-SQL: Round to nearest 15 minute interval

This was answered here How to Round a Time in T-SQL and i think it should work for you to.

CREATE FUNCTION [dbo].[RoundTime] (@Time datetime, @RoundTo float) RETURNS datetime
AS
BEGIN
    DECLARE @RoundedTime smalldatetime, @Multiplier float

    SET @Multiplier = 24.0 / @RoundTo

    SET @RoundedTime= ROUND(CAST(CAST(CONVERT(varchar, @Time, 121) AS datetime) AS float) * @Multiplier, 0) / @Multiplier

    RETURN @RoundedTime
END

-- Usage    
SELECT dbo.RoundTime('13:15', 0.5)

Round to nearest 15 minute interval

Ideally you wouldn't be storing your duration as a decimal - you should be stored as a time if always less than 24 hours or a datetime2 if multiple days are possible.

Convert to a time by converting to a string in acceptable time format. Then use your favourite solution as found here.

select
  -- Original Value
   D.[Value]
   -- As Time
   , T.[Time]
   -- As Time Rounded
   , RT.[Time]
   -- As decimal
   , convert(decimal(9,2),datepart(hour,RT.[Time]) + datepart(minute,RT.[Time]) / 60.0)
from (
  -- Test Values
  values (2.12), (2.02), (0.12)
) D ([Value])
cross apply (
  -- Convert to time datatype
  values (convert(time(0), convert(varchar(8),convert(int, D.[Value])) + ':' + substring(convert(varchar(8),D.[Value] - convert(int, D.[Value])),3,8)))
) T ([Time])
cross apply (
  -- Round using your favourite method
  values (convert(time(0), dateadd(minute, round(datediff(minute, 0, T.[Time]) / 15.0, 0) * 15, 0)))
) RT ([Time]);

T-SQL: Rounding Time in to 15 minutes but only after 5 minutes past the 15min

Just use a couple of date tricks.

This code will give you the top of the hour for the time you're evaluating (minutes effectively removed by adding up the hours since the 0 date in SQL):

select dateadd(hour, datediff(hour, 0, getdate()), 0)

From there, you need a CASE expression to evaluate which quartile of the hour the time in question falls into (just a snippet here):

  case 
    when datepart(minute, dtm) > 50 then 60
    when datepart(minute, dtm) > 35 then 45
    when datepart(minute, dtm) > 20 then 30
    when datepart(minute, dtm) > 5  then 15
    else 0
  end

Put those two pieces together with a DATEADD to decide how many minutes we're adding to that even hour mark:

declare @dtms table (dtm datetime);
insert @dtms (dtm)
values ('2019-07-16T12:05:00'),
       ('2019-07-16T12:06:00'),
       ('2019-07-16T12:21:00'),
       ('2019-07-16T12:29:00'),
       ('2019-07-16T12:35:00'),
       ('2019-07-16T12:38:00'),
       ('2019-07-16T12:56:00')

select
  dtm,
  dateadd(minute, 
    case 
      when datepart(minute, dtm) > 50 then 60
      when datepart(minute, dtm) > 35 then 45
      when datepart(minute, dtm) > 20 then 30
      when datepart(minute, dtm) > 5  then 15
      else 0
    end, dateadd(hour, datediff(hour, 0, dtm), 0)) as rounded
from @dtms;

Results:

+-------------------------+-------------------------+
|           dtm           |         rounded         |
+-------------------------+-------------------------+
| 2019-07-16 12:05:00.000 | 2019-07-16 12:00:00.000 |
| 2019-07-16 12:06:00.000 | 2019-07-16 12:15:00.000 |
| 2019-07-16 12:21:00.000 | 2019-07-16 12:30:00.000 |
| 2019-07-16 12:29:00.000 | 2019-07-16 12:30:00.000 |
| 2019-07-16 12:35:00.000 | 2019-07-16 12:30:00.000 |
| 2019-07-16 12:38:00.000 | 2019-07-16 12:45:00.000 |
| 2019-07-16 12:56:00.000 | 2019-07-16 13:00:00.000 |
+-------------------------+-------------------------+

SQL Server - Rounding Times Up/Down to 30 minute intervals

One method uses time functions -- calculating the number of minutes since time "0" and then using arithmetic to round up or down to 30 minute intervals:

select convert(time, dateadd(minute, ceiling(datediff(minute, 0, start_tm) / 30.0) * 30, 0)),
       convert(time, dateadd(minute, (datediff(minute, 0, end_tm) / 30) * 30, 0))
from (values (convert(time, '09:39'), convert(time, '14:39'))) v(start_tm, end_tm)

---

Related Topics