Calculating Difference between two days excluding weekends in sql server

Suppose we want to find the working days from a date range. It is bit tedious task to calculate date difference because we need to exclude the weekend and holidays. Here we learn how to calculate the working days.

 Let’s learn how we can calculate the working date

First we need to declare ToDate and FromDate

Declare @Fromdate datetime, @Todate datetime,@Temp datetime declare @day int

 Setting the ToDate and FromDate

Set @Fromdate='9/2/2016' set @Todate='9/10/2016'

We need to push the FromDate if it started from Sunday or Saturday to Monday. Because we are excluding the Sunday and Saturday. Lets see how we are excluding this date.

if DATEPART(dw,@Fromdate)=1 set @Fromdate=@Fromdate+1 if DATEPART(dw,@Fromdate)=7 Set @Fromdate=@Fromdate+2

With the help of DATEPART Function we are finding the day.

DATEPART (dw,@Date) will return the numeric value.

Day	Numeric value
Sunday	1
Monday	2
Tuesday	3
Wednesday	4
Thursday	5
Friday	6
Saturday	7

Same we need to set the ToDate value if it is ended at Sunday or Saturday then we need to set it Friday because we are excluding the weekends

if DATEPART(dw,@todate)=1 set @todate=@todate-2 if DATEPART(dw,@todate)=7 Set @todate=@todate-1

Now we get the start and end date excluding date but keep in mind this range having the Weekend also. Now I am going to minus the weekends date.

select @day=dateDiff(dd,@Fromdate,@todate)-(dateDiff(WK,@Fromdate,@Todate)*2)

DATEDIFF(WK,@fromdate,@Todate) it will return the number of week in the given range. Then may you thing why I am multiply by 2. The reason is that this function return the weeks but we need to exclude the two day (Sunday and Saturday).

If Our ToDate fells in Weekend the we are setting it as Friday. I mean we need to count Friday one more time. Finally we need to check if the ToDate Fells in weekend we need to add one more day in the result. See below.

select @day= case when Datepart(dw,@Temp) in (1,7) then @day +1 else @day end

 Finally we get the working date

See the full sql script below.

Declare @Fromdate datetime, @Todate datetime,@Temp datetime declare @day int Set @Fromdate='9/2/2016' set @Todate='9/10/2016' if DATEPART(dw,@Fromdate)=1 set @Fromdate=@Fromdate+1 if DATEPART(dw,@Fromdate)=7 Set @Fromdate=@Fromdate+2 Set @temp=@todate if DATEPART(dw,@todate)=1 set @todate=@todate-2 if DATEPART(dw,@todate)=7 Set @todate=@todate-1 select @day=dateDiff(dd,@Fromdate,@todate)-(dateDiff(WK,@Fromdate,@Todate)*2) select @day= case when Datepart(dw,@Temp) in (1,7) then @day +1 else @day end Select @day as [Number of working day]

See the Output

  

Verify it from calendar also

  

Taking one more example.

FromDate=9/5/2016

And ToDate=9/17/2016

  

Hope this is useful to you. Thanks!!!