The topic is very similar to a recent 'Users that need help' query in SQLServerCentral.com. He asked how many workdays (mon-fri) are there between two given dates. And what's interesting is that I too recently was hit with this problem only to find the same weird stuff on the Internet that Peter Larson mentioned. And I too decided to hack this problem on my own.

Whereas Peter Larson decided to take a general approach, I decided to take an approach that answers just that question. Thus my function looks as follows (it may need to take advantage of the code he uses that takes strips the time portion of a date):

create function trx_workdays(@p_startdate datetime, @p_enddate datetime) returns integer as

begin

declare @dwdest int

declare @padded_enddate datetime

declare @padded_workdays int

declare @DW int

declare @diff int

if @p_startdate is null or @p_enddate is null

  return 0

set @padded_enddate=@p_enddate

set @padded_workdays=0

-- pad end date so that difference becomes a multiple of 7 days;

-- we also need to count the number of weekdays in the days we added for the padding;

set @dwdest=datepart(dw,@p_startdate)-1

if @dwdest=0 set @dwdest=7

while datepart(dw,@padded_enddate)<>@dwdest -- loops no more than 6 times

  begin

    set @padded_enddate=dateadd(d,1,@padded_enddate)

    set @DW=datepart(dw,@padded_enddate)

    -- 1=saturday 7=sunday

    if @DW<>1 and @DW<>7 set @padded_workdays=@padded_workdays+1   

  end

set @diff=datediff(d,@p_startdate,@padded_enddate)+1

-- subtract number of intervening saturdays/sundays and subtract number of workdays we added for the padding

return @diff-@diff/7*2-@padded_workdays

end

go

When I have some time I will benchmark this against Peter Larson's and other solutions.