Find working days between two dates in SQL Server

Many a times it is required to find working days between two dates. We do know that DATEDIFF function can be used to find differences between two dates; however the working days calculation excludes weekends and holidays. This solution doesn’t consider holidays. The function for the same is given below.

CREATE FUNCTION [dbo].fn_CountWeekDays
(
    @fromdate Datetime,
    @todate Datetime
)
RETURNS TABLE AS RETURN
(

SELECT
   (DATEDIFF(dd, @fromdate, @todate) + 1)
  -(DATEDIFF(wk, @fromdate, @todate) * 2)
  -(CASE WHEN DATENAME(dw, @fromdate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @todate) = 'Saturday' THEN 1 ELSE 0 END)
  As NoOfWeekDays

)

The above function is an inline table valued function which accepts two parameters @fromdate and @todate. The statement DATEDIFF(dd,@fromdate,@todate) + 1 gives the number of dates between the two dates. The statement DATEDIFF(wk,@fromdate,@todate) gives the number of weeks between dates and * 2 gives us the weekend (Saturday and Sunday) count. The next two statements excludes the day if it’s a Saturday or Sunday.

The output from the function is given below.

   

Find working days between two dates SQL Server

 

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

   

5 Comments on “Find working days between two dates in SQL Server”

  1. declare @startdate date,
    @enddate date, @a integer
    set @startdate=’2016-04-01′
    set @enddate=’2016-04-30′
    set @a=0
    select @startdate
    select @enddate
    while @startdate<=@enddate
    Begin
    if Datename(dw,@startdate)in('Monday','Tuesday','Wednesday','Thursday','Friday')
    begin
    set @a=@a+1
    end
    set @startdate=DATEADD(d,1,@startdate)
    end;
    select @a

  2. Hello everyone,
    I am struggling with the following problem. I need to calculate hours between 2 dates. For example let say that the start day is 01.03.2018 -Thursday and the end day is 06.03.2018 – Tuesday. I want to be able to calculate only the working hours between these 2 days. Let say that working hours are between 9 and 18. I tried a few things but no luck. Any help will be much appreciated. Thanks

  3. thnk you for this function i really lookiing for it but a find it at the end thnks , a hane a small qu’estion what about if a want to add the Holidays also to the weekends

Leave a Reply

Your email address will not be published.