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

You might prefer to use datediff(day, '19800107', yourDate) / 7 (where 7-Jan-1980 is a known Monday), so that you don't have to worry about when the system thinks that a week has started. Also, this mechanism is very easy to adapt to different weeks without having to consider the value of @@DATEFIRST, just pick a known Sunday, or a known Thursday, or whatever.