Calculate number Hours from Date range excluding weekend dates in sql

Question

I have a range of date i.e start date 19/05/2017 till end date 25/05/2017. I want to get the hours calculated in between them without including weekends i.e friday and saturday. For example:

7 days have 7*24= 168 hrs 5 days excluding friday and Saturday will give 120hrs.

Any function that can be used in another query?

Duplicate: [Count work days between two dates](https://stackoverflow.com/questions/252519/count-work-days-between-two-dates) — streetturtle, May 23 '17 at 17:39
Possible duplicate of [Count work days between two dates](https://stackoverflow.com/questions/252519/count-work-days-between-two-dates) — SqlZim, May 23 '17 at 18:04

score 0 · Answer 1 · answered May 24 '17 at 08:26

create function dbo.GetHoursWithoutWeekends (@date1 date, @date2 date)
returns int
as
begin

    declare @hours int


    set @hours = 24 * (DATEDIFF(day, @date1, @date2) + 1 - (
                                                select
                                                    count(DATEADD(day, t.Rbr - 1, @date1)) as WeekEndCount
                                                from (
                                                    select
                                                        ROW_NUMBER() over (order by sc1.name) as Rbr
                                                    from sys.syscolumns sc1
                                                        cross join sys.syscolumns sc2
                                                ) t
                                                where DATEADD(day, t.Rbr - 1, @date1) between @date1 and @date2
                                                    and DATEPART(weekday, DATEADD(day, t.Rbr - 1, @date1)) in (5, 6)
                                            ));

return @hours;

end
GO

set datefirst 1

select dbo.GetHoursWithoutWeekends ('20170519', '20170525')

Calculate number Hours from Date range excluding weekend dates in sql

1 Answers1