week number in a month (1 Viewer)

[rkrause]

I want to find out a week number in a month in a query. for example this gives me the week number in a year
datepart(wk,p1labdate) as week, but i would want to know somethign similar for a month for example for may 2011 and june 11.

5/1/11 would be week 1
5/8/11 would be week 2
5/15/11 would be week 3
5/22/11 would be week 4
5/29/11 would be week 5

6/5/11 would be week 1
6/12/11 would be week 2
6/19/11 would be week 3
6/26/11 would be week 4

 

[boblarson]

What day is considered your start of the week?

EDIT: Okey that was stupid of me. Of course you told me with the dates.

 

[rkrause]

i was able to find this, but it doesnt work when the first day of the month falls on a weekend. for this year January 1 is on a saturday thus it says its in week 5. when truly its a new month it should be a short week 1 day week. im guessing the week 5 comes from December 2010.

DATEPART(DAY,p1labdate - 1) / 7 + 1 AS theWeekWithinMonth