Use a calendar table like in the example below:CREATE TABLE #calendar(Dt DATETIME NOT NULL PRIMARY KEY CLUSTERED, isWeekDay BIT);;WITH cte AS( SELECT CAST('20130101' AS DATETIME) AS Dt UNION ALL SELECT DATEADD(dd,1,dt) FROM cte WHERE Dt < '20131231')INSERT INTO #calendarSELECT dt, CASE WHEN DATEDIFF(dd,0,dt)%7 >=5 THEN 0 ELSE 1 END FROM cteOPTION (MAXRECURSION 0);DECLARE @startDate DATETIME = '20130501';DECLARE @endDate DATETIME = '20130620';SELECT SUM(CASE WHEN isWeekday = 1 THEN 1 ELSE 0 END)FROM #calendar WHERE Dt >= @startDate AND Dt < @endDateDROP TABLE #calendar;
If you can guarantee that the startdate and enddate are weekdays, then you can use a formula like this:DECLARE @startDate DATETIME = '20130501';DECLARE @endDate DATETIME = '20130620';SELECT DATEDIFF(dd,@startDate,@endDate)-2*DATEDIFF(wk,@startDate,@endDate);
But in general, a calendar table is the easiest.