5/16/2018

Count Week days between given two dates excluding weekends (Sat & Sun)


Count Week days between given two dates excluding weekends (Sat & Sun)

Below query will return count of days only for week days, after excluding Saturday and Sunday.

declare @firstdate Date

declare @seconddate Date

 

set @firstDate = convert(date, '2018-05-01')

set @seconddate = convert(date, '2018-05-17')

 

 

select (datediff(dd, @firstDate, @secondDate)+1) -

    (( DateDiff(wk, @firstDate, @secondDate) * 2) -

      case when datepart(dw, @FirstDate) = 7 then 1 else 0 end -

      case when datepart(dw, @secondDate) = 7 then -1 else 0 end)

 

 SELECT

    (DATEDIFF(dd, @firstDate, @secondDate) + 1)

   -(DATEDIFF(wk, @firstDate, @secondDate) * 2)

   -(CASE WHEN DATENAME(dw, @firstDate) = 'Sunday' THEN 1 ELSE 0 END)

   -(CASE WHEN DATENAME(dw, @secondDate) = 'Saturday' THEN 1 ELSE 0 END)