Rounding script works to the nearest 15 minutes based on duration, how to shift this threshold by negative 30 seconds for decimal hours

Question

I have a script which rounds a time length to the nearest 15 minutes. So for example 00:23:00 minutes to 00:37:59 duration will return 0.5 (decimal 30 minutes) etc continuous around the clock. I am trying to now get the script to work in half minutes so I need to shift this by 30 second offset for the rounding, so 00:22:29 seconds will go to 0.25 and 00:22:30 will round to 0.5. Here is the original script. So this needs to return 0.5 not 0.25. If you change the @finish to 08:22:29 it needs to then return 0.25. Thank you

declare @start datetime = '2020-03-01 08:00:00:000'
declare @finish datetime = '2020-03-01 08:22:30:000'

select
(CAST(DATEPART(hh, (DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( second, ( 15 * 60 ) / 2, (convert(varchar(5),(@finish - @start),108)) ) ) / 15 ) * 15, 0 ))) AS float) +
    CAST(DATEPART(mi, (DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( second, ( 15 * 60 ) / 2, (convert(varchar(5),(@finish - @start),108)) ) ) / 15 ) * 15, 0 ))) AS float) / 60 +
        CAST(DATEPART(ss, (DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( second, ( 15 * 60 ) / 2, (convert(varchar(5),(@finish - @start),108)) ) ) / 15 ) * 15, 0 ))) AS float) / 3600)

Your problem is that you divide two integers and the result would be an integer. After that, you only turn an integer to float. Try 2.00 and 15.00 as denominators. In this way, you might need to change your solution logic — JoPapou13, Sep 17 '20 at 15:00

score 2 · Accepted Answer · answered Sep 17 '20 at 15:10

2

Here's another possible solution, using CASE to classify the modulo seconds over an hour:

SELECT DATEDIFF(second,@start,@finish) / 3600 +
CASE WHEN DATEDIFF(second,@start,@finish) % 3600 <  450 THEN 0.0
     WHEN DATEDIFF(second,@start,@finish) % 3600 < 1350 THEN 0.25
     WHEN DATEDIFF(second,@start,@finish) % 3600 < 2250 THEN 0.5
     WHEN DATEDIFF(second,@start,@finish) % 3600 < 3150 THEN 0.75
     ELSE 1
END

answered Sep 17 '20 at 15:10

Ed Harper

21,127
4
54
80

That works really well, thank you! clever how it handles anything over 1 hour. Thank you! – user2364055 Sep 18 '20 at 08:16

score 0 · Answer 2 · answered Sep 17 '20 at 14:54

0

Solution is to add a dateadd(second, 30, @finish) I believe. Not the start time, this will then either push the result to over 00:23 if 00:22:30 and above or keep the length below 00:23. This should do the trick

answered Sep 17 '20 at 14:54

user2364055

33
5

score 0 · Answer 3 · answered Sep 17 '20 at 15:28

0

In your solution, the problem is starting from the division of two integer numbers that would result to an integer. You can check for a solution.

Since you are dealing with time, it might be appropriate to use the CEILING function. If you think that this way is more appropriate, you could use:

declare @start datetime = '2020-03-01 08:00:00:000'
declare @finish datetime = '2020-03-01 08:22:30:000'

SELECT CEILING( DATEDIFF( ss, @start, @finish)/ (60*60.00) * 4) / 4

answered Sep 17 '20 at 15:28

JoPapou13

753
3
8

Hi, thank you for your answer. Unfortunately this doesn't round down to the nearest 15 minutes, only up though. So 00:22:29 would need to round to 0.25 and 00:22:30 would round up to 0.5. In this example anything over 00:15 rounds to 0.5. Not quite working sorry – user2364055 Sep 18 '20 at 08:14

Rounding script works to the nearest 15 minutes based on duration, how to shift this threshold by negative 30 seconds for decimal hours

3 Answers3