How to return weeks of data (Sun to Sun)

I'开发者_如何学Pythonm trying to return some data based on a series of weeks. This is going to be for an SSRS report.

For example, I need to return all rows which are dated from 3rd July to 9th July inclusive (Sun-Sat), name it Week 1 of the month. Then 10th Jul to 16th July, which is Week 2, and so forth. The hardest part is having it continue on between months. For example, 26th June to 2nd July.

I'm sure I can use DATEPART to do this somehow, but really have no idea how. Can someone offer assistance?

Thank you in advance.

DATEPART(week, DateField) will give you the week of the year, which will cover your criteria. Week of the month is not a great criteria to use since it's so amorphous.

You can get the week of the year for the first of the month by returning DATEPART(week, '7/1/2011') or whatever, which will give you a starting point.

EDIT:

For your additional criteria from the comments:

SELECT <fields>
FROM MyTable
WHERE YEAR(DateField) = 2011
AND DATEPART(week, Datefield) BETWEEN 
    DATEPART(week, '6/1/2011') AND DATEPART(week, (DATEADD(DAY, -1, (DATEADD(Month, 1,     '6/1/2011')))))

You may want to check the closing parentheses count, but basically this says:

• Year 2011
• Week of 6/1/2011 through Week of 6/30/2011

The extra date calculations are to get to 7/1/2011 (month +1) then back a day to 6/30/2011. The alternative is to hardcode date breaks for each month. This way you can also paramterize the dates and concatenate like

CAST(@Month + '/1/' + @Year as smalldatetime)

I suggest the use of something similar to a date dimension. There are tons of examples of how you do this. Here is one: http://prologika.com/CS/forums/p/517/2094.aspx

Well I will give a more exact answer because no one has left anything yet

Declare @myDate datetime = getdate()

select DATEPART(week, @myDate) - DATEPART(week,CONVERT(DATETIME, DATENAME(mm, @myDate)+"/01/"+ DATENAME(yyyy, @myDate) ))-1

This should give you a good approximation you might want to check my parens.