If you use Microsoft Outlook, you know that when you set up a meeting or appointment, you can select an option like “The 4th Monday of every month�. Well, today I had to correlate this to a SQL statement and it through me for a loop: Well, yeah, I used a loop. Check it out
DECLARE @tempDate DATETIME
DECLARE @maxDate DATETIME
SET @maxDate = ’07/01/2006′
SET @tempDate = ’07/01/2005′
WHILE @tempDate <= @maxDate BEGIN
SELECT DATEADD(wk,3,
DATEADD(wk, DATEDIFF(wk,0,
DATEADD(dd,6-DATEPART(DAY,@tempDate),@tempDate)
), 0))
SET @tempDate = DATEADD(mm,1,@tempDate)
END
I am getting the first Monday of the month in @tempDate and adding 3 weeks to it. Then I just loop through from @tempdate to @maXDate. Works like a champ.
Steve Novoselac's Blog 