get DATEDIFF excluding weekends using sql server

I found when i used this there was a problem when d1 fell on saturday. Below is what i used to correct this.

declare @d1 datetime, @d2 datetime
select @d1 = '11/19/2011' ,  @d2 = '11/28/2011'

select datediff(dd, @d1, @d2) +case when datepart(dw, @d1) = 7 then 1 else 0 end - (datediff(wk, @d1, @d2) * 2) -
 case when datepart(dw, @d1) = 1 then 1 else 0 end +
 case when datepart(dw, @d2) = 1 then 1 else 0 end

Example query below, here are some details on how I solved it.

Using DATEDIFF(WK, ...) will give us the number of weeks between the 2 dates. SQL Server evaluates this as a difference between week numbers rather than based on the number of days. This is perfect, since we can use this to determine how many weekends passed between the dates.

So we can multiple that value by 2 to get the number of weekend days that occurred and subtract that from the DATEDIFF(dd, ...) to get the number of weekdays.

This doesn't behave 100% correctly when the start or end date falls on Sunday, though. So I added in some case logic at the end of the calculation to handle those instances.

You may also want to consider whether or not the DATEDIFF should be fully inclusive. e.g. Is the difference between 9/10 and 9/11 1 day or 2 days? If the latter, you'll want to add 1 to the final product.

declare @d1 datetime, @d2 datetime
select @d1 = '9/9/2011',  @d2 = '9/18/2011'

select datediff(dd, @d1, @d2) - (datediff(wk, @d1, @d2) * 2) -
       case when datepart(dw, @d1) = 1 then 1 else 0 end +
      case when datepart(dw, @d2) = 1 then 1 else 0 end