Using SQL DateAdd algorithm, excluding weekends

Question

I need to write a simple query that returns three weekdays prior to a given date. I don't want to create a calendar table. Is there an algorithm that uses dateadd() and datepart() that i can use to get this result?

Answer 1

This is something I used before as a template that I found very useful:

DECLARE @DateOld datetime, @DateNew datetimeSET @DateOld = '10-Sep-2005'SET @DateNew = GETDATE()
SET DATEFIRST 1
SELECT DATEDIFF (day, @DateOld, @DateNew) - (2 * DATEDIFF(week, @DateOld, @DateNew)) - CASE WHEN DATEPART(weekday, @DateOld + @@DATEFIRST) = 1 THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, @DateNew + @@DATEFIRST) = 1 THEN 1 ELSE 0 END 

Source: http://sqlcode.blogspot.com/2007/07/calculate-number-of-business-days.html

Answer 2

I ended up using a while loop to count the number of weekdays.

i keep cycling back one day at a time until i get the the desired number of weekdays using this weekday logic:

set @isweekday=case when (DATEPART(dw, @tempdate) + @@DATEFIRST) % 7 NOT IN (0, 1) then 1 else 0 end