question

This, like many other things can be solved using a Numbers table. Without the need to exclude saturday and sunday, it would easily be solved using DATEDIFF alone. It could probably be solved more efficiently using a some combination of date functions and pure mathematics to work out how many days to exclude, but I think this way is pretty straightforward and the performance is ok. I've tested it using a table with two columns (date1 and date2) and 100 rows with different values, and calculated number of mondays-fridays in less than a second. --First create a Numbers table, if it does not already exist. SELECT TOP 11000 IDENTITY(int,0,1) AS N INTO dbo.Numbers FROM sys.all_columns c1 CROSS JOIN sys.all_columns c2 --Needs to be index to not mess up performance CREATE CLUSTERED INDEX ixc_n ON dbo.Numbers(N) --Weekday 1 should be monday SET DATEFIRST 1 --Declare date variables DECLARE @d1 datetime, @d2 datetime SET @d1 = '20110101 12:00:00.000' SET @d2 = '20110115 12:00:00.000' --Now count the days between @d1 and @d2 SELECT COUNT(*) FROM dbo.Numbers WHERE N < DATEDIFF(day,@d1, @d2) AND DATEPART(weekday,dateadd(day,n,@d1)) not in (6,7)