A Journey 2 Eternity

Create a Weekly Date Bucket

Posted on: December 23, 2011


DECLARE @startdate SMALLDATETIME = '2011-01-01'
DECLARE @stopdate SMALLDATETIME = '2011-12-31'

-- Create a Table
DECLARE @weekTable TABLE (
Id INT IDENTITY (1, 1),
StartDate SMALLDATETIME,
EndDate SMALLDATETIME
)

-- Find Monday at that week
DECLARE @currentDate SMALLDATETIME = DATEADD(d, 2 - DATEPART(dw, @startdate), @startdate)

WHILE @currentDate <= @stopdate BEGIN
INSERT INTO @weekTable VALUES (@currentDate, DATEADD(d, 6, @currentDate))
SET @currentDate = DATEADD(ww, 1, @currentDate)
END

-- Return the results
SELECT Id 'Week #', StartDate 'Start Date', EndDate 'End Date'
FROM @weekTable

Advertisements
Tags: ,

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

Pages

Categories

December 2011
M T W T F S S
« Nov   Jun »
 1234
567891011
12131415161718
19202122232425
262728293031  

Blog Stats

  • 26,968 hits
%d bloggers like this: