CODEWITHSUNDEEP
sql-server
brndnmlk
brndnmlk

Reputation: 3

round results of datediff (float) to nearest half hour

I am calculating the number of hours from two smalldatetimes. The clients want the hours rounded to the nearest half hour. My calculation is: 

hours = datediff(mi,ActualStart,ActualEnd)/60.0

This gives me a float, which I cannot round the way time is rounded. I do know how to round the start and end times, but would rather apply the rounding to the total hours.

I'm using SQL Server 2012

Upvotes: 0

Views: 1158

Answers (3)

Kevin Swann
Kevin Swann

Reputation: 1038

DATEADD( minute, ( DATEDIFF( minute, 0,                          dateTimeX   ) / 30 ) * 30, 0 ) AS dateTimeRoundDown,
DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( minute, 30 / 2, dateTimeX ) ) / 30 ) * 30, 0 ) AS dateTimeRoundNearest,
DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( minute, 30    , dateTimeX ) ) / 30 ) * 30, 0 ) AS dateTimeRoundUp

For more details, see this SO question & answer

Unit Test

DECLARE @start DATETIME  = '2017-04-20 21:00:00'
DECLARE @end   DATETIME  = '2017-04-20 23:00:00'

;WITH CTE_dateTimes AS
(
    SELECT @start AS dateTimeX
    UNION ALL
    SELECT DATEADD( minute, 1, dateTimeX )
    FROM CTE_dateTimes
    WHERE DATEADD( minute, 1, dateTimeX ) <= @end
)
SELECT dateTimeX,
   DATEADD( minute, ( DATEDIFF( minute, 0,                          dateTimeX   ) / 30 ) * 30, 0 ) AS dateTimeRoundDown,
   DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( minute, 30 / 2, dateTimeX ) ) / 30 ) * 30, 0 ) AS dateTimeRoundNearest,
   DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( minute, 30    , dateTimeX ) ) / 30 ) * 30, 0 ) AS dateTimeRoundUp

FROM CTE_dateTimes
OPTION ( MAXRECURSION 200 )

Upvotes: 3

David Dubois
David Dubois

Reputation: 3932

Try this: 

 round( 48 * ( cast(ActualEnd as float)-cast(ActualStart as float) ),0) /2.0 as HoursRoundedToHalfHour,

Casting a datetime value to float gives you the number of days since a particular date.

Subtracting one from the other gives the number of days between the two datetimes.

Multiply this by 48 to give the number of half-hour intervals.

Calling Round(x,0) rounds this to the nearest integer.

Dividing by 2.0 converts it to Hours.

Example:

create table Times ( ActualStart smalldatetime, ActualEnd smalldatetime )


insert into Times values
 ('20170125 01:00:00','20170125 01:10:00'),('20170125 01:00:00','20170125 01:15:00'),('20170125 01:00:00','20170125 01:25:00'),('20170125 01:00:00','20170125 01:40:00'),('20170125 01:00:00','20170125 01:55:00'),('20170125 01:00:00','20170125 02:05:00'),('20170125 01:00:00','20170125 02:55:00'),('20170125 01:00:00','20170125 04:14:00'),('20170125 01:00:00','20170125 22:05:00');


select 
  ActualStart,ActualEnd, 
  datediff(mi,ActualStart,ActualEnd) as [Minutes], 
  datediff(mi,ActualStart,ActualEnd)/60 as [Hr],       
  datediff(mi,ActualStart,ActualEnd)%60 as [Min],
  round( 48 * ( cast(ActualEnd as float)-cast(ActualStart as float) ),0) /2.0 as [HoursRoundedToHalfHour]
from Times


ActualStart             ActualEnd               Minutes     Hr          Min         HoursRoundedToHalfHour
----------------------- ----------------------- ----------- ----------- ----------- ----------------------
2017-01-25 01:00:00     2017-01-25 01:10:00     10          0           10          0
2017-01-25 01:00:00     2017-01-25 01:15:00     15          0           15          0.5
2017-01-25 01:00:00     2017-01-25 01:25:00     25          0           25          0.5
2017-01-25 01:00:00     2017-01-25 01:40:00     40          0           40          0.5
2017-01-25 01:00:00     2017-01-25 01:55:00     55          0           55          1
2017-01-25 01:00:00     2017-01-25 02:05:00     65          1           5           1
2017-01-25 01:00:00     2017-01-25 02:55:00     115         1           55          2
2017-01-25 01:00:00     2017-01-25 04:14:00     194         3           14          3
2017-01-25 01:00:00     2017-01-25 22:05:00     1265        21          5           21

Upvotes: 0

iamdave
iamdave

Reputation: 12243

Here is one way you could achieve what you are after, which should give you enough to make a start on your problem:

declare @t table(s datetime, e datetime);
insert into @t values
 ('20170125 01:00:00','20170125 01:10:00'),('20170125 01:00:00','20170125 01:15:00'),('20170125 01:00:00','20170125 01:25:00'),('20170125 01:00:00','20170125 01:40:00'),('20170125 01:00:00','20170125 01:55:00'),('20170125 01:00:00','20170125 02:05:00'),('20170125 01:00:00','20170125 02:55:00'),('20170125 01:00:00','20170125 04:14:00'),('20170125 01:00:00','20170125 22:05:00');

select datediff(mi,s,e) as MinutesDiff

        ,datediff(mi,s,e) / 60 as HoursDiff
        ,datediff(mi,s,e) % 60 as MinutesRemain

        -- Take the Minutes Remaining after all the full hours are removed,
        -- and integer divide by 15 to get the number of full quarter hours
        -- and then round to the nearest number of minutes to add on.
        ,case round(datediff(mi,s,e) % 60 / 15,0)
            when 0 then 0
            when 1 then 30
            when 2 then 30
            when 3 then 60
            end as MinutesRounded
from @t;

Output:

+-------------+-----------+---------------+----------------+
| MinutesDiff | HoursDiff | MinutesRemain | MinutesRounded |
+-------------+-----------+---------------+----------------+
|          10 |         0 |            10 |              0 |
|          15 |         0 |            15 |             30 |
|          25 |         0 |            25 |             30 |
|          40 |         0 |            40 |             30 |
|          55 |         0 |            55 |             60 |
|          65 |         1 |             5 |              0 |
|         115 |         1 |            55 |             60 |
|         194 |         3 |            14 |              0 |
|        1265 |        21 |             5 |              0 |
+-------------+-----------+---------------+----------------+

Upvotes: 0

Related Questions