SQL DATEDIFF count hours of days excluding weekend

Question

So i am trying to count the amount of hours between two dates excluding hours on weekends.

    SELECT
  (DATEDIFF(HOUR,'2019-08-23 03:00:00', '2019-08-25 09:00:00'))
  -(DATEDIFF(wk, '2019-08-23 03:00:00', '2019-08-25 09:00:00') * 2 * 24)  
 -(CASE WHEN DATENAME(dw, '2019-08-23 03:00:00') = 'Sunday' THEN 24 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, '2019-08-25 09:00:00') = 'Saturday' THEN 24 ELSE 0 END)   
  as 'expecting 21 hours'
  ,

  (DATEDIFF(HOUR,'2019-08-23 03:00:00', '2019-08-26 07:00:00') )
  -(DATEDIFF(wk, '2019-08-23 03:00:00', '2019-08-26 07:00:00') * 2 * 24)
 -(CASE WHEN DATENAME(dw, '2019-08-23 03:00:00') = 'Sunday' THEN 24 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, '2019-08-26 07:00:00') = 'Saturday' THEN 24 ELSE 0 END) 
  as 'expecting 28 hours'
  ,  

  (DATEDIFF(HOUR,'2019-08-24 03:00:00', '2019-08-26 07:00:00') )
  -(DATEDIFF(wk, '2019-08-24 03:00:00', '2019-08-26 07:00:00') * 2 * 24)
 -(CASE WHEN DATENAME(dw, '2019-08-24 03:00:00') = 'Sunday' THEN 24 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, '2019-08-26 07:00:00') = 'Saturday' THEN 24 ELSE 0 END)
   as 'expecting 7 hours'

The caluclation is wrong as the output is

expecting 21 hours  | expecting 28 hours    | expecting 7 hours
        6           |         28            |           4

So it seems as i have to handle the times of the dates somwhow better, but how?

Consumable sample data and *readable* expected results will greatly help us help you. — Thom A, Aug 22 '19 at 13:53
It is based on the idea of counting full working days: https://stackoverflow.com/a/252533/1070022 — Merion, Aug 22 '19 at 14:01
Using a combination of a calendar table and a tally table would this a trivial task. Just saying. — Zohar Peled, Aug 22 '19 at 14:03

score 0 · Answer 1 · answered Aug 22 '19 at 14:27

; with base as (
    select * from (values (1), (2), (3), (4), (5), (6), (7), (8)) as d(number)
)
, numbers as (
    select row_number() over (order by b1.number) as number
    from base b1
    cross join base b2
    cross join base b3
    cross join base b4
)
, hours as (
    select dateadd(hour, number, '20190823') as hourtime, case when DATENAME(dw,  dateadd(hour, number, '20190823')) in ('Sunday', 'Saturday') then 0 else 1 end as hourcount
    from numbers
)
select sum(hourcount)
from hours 
where hourtime >= '2019-08-23 03:00:00' and hourtime < '2019-08-25 09:00:00'
--where hourtime >= '2019-08-23 03:00:00' and hourtime < '2019-08-26 07:00:00'
--where hourtime >= '2019-08-24 03:00:00' and hourtime < '2019-08-26 07:00:00'

score 0 · Answer 2 · answered Aug 22 '19 at 14:33

Using common table expressions to build a tally table and a calendar table on the fly, here's a simple, readable solution:

DECLARE @StartDate As DateTime = '2019-08-23T03:00:00',
        @EndDate As DateTime = '2019-08-25T09:00:00';

WITH Tally(n) AS
(
    SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY @@SPID)
    FROM sys.objects 
), Calendar AS
(
    SELECT  DATEADD(DAY, n-1, '2019-08-01') As TheDate,
            IIF(DATEPART(WEEKDAY, DATEADD(DAY, n-1, '2019-08-01')) IN (1,7), 1, 0) As IsWeekEnd
    FROM Tally
)

SELECT DATEDIFF(HOUR, @StartDate, @EndDate) - (COUNT(*) * 24)
FROM Calendar
WHERE TheDate >= CAST(@StartDate As Date)
AND TheDate <= CAST(@EndDate As Date)
AND IsWeekEnd = 1

score 0 · Answer 3 · answered Aug 22 '19 at 23:26

Using Recursive CTE will help here, we take the first date, for the first record, then we add a second row that will have the date of the second day without the time part, and go on till we get to the last date. each record we will add a date ending so we can count how many hours on that date, plus we can see what day of week it is. then we sum the hours of all the non weekend days.

DECLARE @StartDate As DateTime = '2019-08-23 03:00:00',
        @EndDate As DateTime = '2019-08-25 09:00:00';
--DECLARE @StartDate As DateTime = '2019-08-23 03:00:00',
--        @EndDate As DateTime = '2019-08-26 07:00:00';
--DECLARE @StartDate As DateTime = '2019-08-24 03:00:00',
--      @EndDate As DateTime = '2019-08-26 07:00:00';

;with cte as (
select @StartDate [datestart],DATENAME(WEEKDAY,@StartDate) [DayName],dateadd(SECOND,-1,cast(dateadd(day,1,CAST(@StartDate as date)) as datetime)) [dateEnd]
union all
select dateadd(second,1,cte.dateEnd)
    ,DATENAME(WEEKDAY,dateadd(second,1,cte.dateEnd)) [DayName]
    ,case when dateadd(hour,24,cte.dateEnd)>@EndDate then @EndDate else dateadd(hour,24,cte.dateEnd) end    
  from cte where cte.dateEnd<@EndDate
)

select SUM(DATEDIFF(hour,cte.datestart,dateadd(second,1,cte.dateEnd))) [TotalHours] from cte 
where [DayName] not in ('Saturday','Sunday')

The output is as follow for each :

expecting 21 hours  | expecting 28 hours    | expecting 7 hours
        21          |         28            |           7

SQL DATEDIFF count hours of days excluding weekend

3 Answers3