SQL: How To Do Date Reoccurrence

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s