Get Weeks in SQL

Question

I'm certain I'm overlooking something very basic here but I can't wrap my head around it after querying and googling for about an hour.

I have a datetable already in existence, if that helps. It is populated with this past years' worth of dates and is updated nightly via a stored proc that gets run by a job on the server.

What I need to do is pull Monday-Sunday weeks out so I can properly join them to a tracking table to get a weekly hit graph up. I don't want to write out 52 gradually larger select statements and union them for obvious reasons, and I don't like the idea of cursors.

I've already done this with months and days but the nature of the weeks confuses me for some reason, or it's inherently different.

Any thoughts on how to automate the process without cursors or a huge select/union? I'll go for the cursor and dump it into a table nightly if absolutely necessary but I hope not.

FYI my desired format at the end of it would be:

[Week number] | [StartDate] | [EndDate]

For each week

Answer 1

I could be off course with what you're wanting, but it sounds like you want this kind of thing:

-- e.g. count of records grouped by week, for 2009

SELECT DATEPART(wk, DateField) AS WeekNumber, COUNT(*) AS HitsForWeek
FROM SomeTable
WHERE DateField >= '20090101' AND DateField < '20100101'
GROUP BY DATEPART(wk, DateField)

Answer 2

One added thing -- AdaTheDev's answer is right, but by default it will give you Sunday through Saturday weeks. If you want Monday through Sunday weeks, you have to execute

SET DATEFIRST 1

to set the first day of the week to Monday (1). You can query @@DateFirst to see what your setting is -- by default it is 7 (Sunday) in the US English configuration.

Answer 3

I have found weeks tricky too, below is the code I use to convert a date into it's date week group. Below are examples which will return weekending Friday or Week commencing Monday without having to maintain a supporting table. You can also adjust them to use Saturday and Sunday if that is what you want to use.

--Returns the weekending (Friday) for a particular day
SELECT DATEADD(day, -DATEPART(weekday,yourDate), yourDate)+6

--Returns the week commencing (Monday) for a particular day
SELECT DATEADD(day, -DATEPART(weekday,yourDate)+2, yourDate)

Happy coding :)