Want a SQL to calculate number of work days between 2 dates

Question

I want SQL to calculate number of work days between 2 dates. for example, the start date is 3/1/2017 and end date is 3/10/2017, so the result should be 8 days not 10 days. how to achieve that in SQL server. thanks

http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/ — Ofir Winegarten, Mar 06 '17 at 06:33
http://stackoverflow.com/questions/7388420/get-datediff-excluding-weekends-using-sql-server — Sam, Mar 06 '17 at 07:09
Possible duplicate of [Count work days between two dates](http://stackoverflow.com/questions/252519/count-work-days-between-two-dates) — splattne, Mar 06 '17 at 07:10

score 0 · Answer 1 · edited Jan 18 '21 at 12:32

If you want to show the days which are not in saturday and sunday within a date range. Then,

Query

declare @start as date = '2017-03-01';
declare @end as date = '2017-03-10';
declare @i as int = 0;
declare @j as int = datediff(day, @start, @end)
declare @t as table([date] date, [dayname] varchar(50));

while(@i <= @j)
begin
    insert into @t([date], [dayname]) values
    (dateadd(day, @i, @start), Datename(weekday, dateadd(day, @i, @start)));
    set @i += 1;
end

select * from @t
where [dayname] not in('Saturday', 'Sunday');

**Demo Here**

score 0 · Answer 2 · answered Mar 06 '17 at 07:08

You can try this Query:

;with work_days as (
    -- CTE of work days
    select 2 [day_of_week] union -- Monday
    select 3 union               -- Tuesday
    select 4 union               -- Wednesday
    select 5 union               -- Thursday
    select 6                     -- Friday
 )
,dates_between  as (
    -- recursive CTE, for dates in range
    select cast('20170301' as date) [day] 
    union all
    select dateadd(day,1,[day]) from dates_between
      where [day]=[day] and [day]<'20170310'
)
select * 
from dates_between join work_days 
  on work_days.day_of_week = DATEPART(dw, dates_between.[day])
order by dates_between.[day]
OPTION (MAXRECURSION  0) -- if dates range more than 100 days

Want a SQL to calculate number of work days between 2 dates

2 Answers2