Try (not tetsed much)

SELECT (DAY(@dt)  
	+ (((8 - 
		(1 + ((7 - (
			DATEPART(dw, DATEADD (month, DATEDIFF (month, 0, @dt), 0))-1) -- DOW of the first of month
					) % 7) -- day of the first Sunday in the month
		)  
		))%7) -- # of days  to add to make the first week full 7 days
	-1)/7 + 1

>I'm trying to determine what week# a given date falls in based off the date. Some months have 4 weeks and some have 5. In this example, the month of June has 5 weeks, but only the 1st and 2nd of June are in the first week, the rest are left over days from May.
>
>1-2 falls in the 1st week
>3-9 are in the 2nd
>10-16 are in the 3rd
>17-23 are in the 4th
>24-30 are in the 5th
>
>
>So for a given date, 6/4/2007 for example, what week# within June (based off a calendar) does that date fall on. In this case it would be the second week since June 1st falls on a friday. Does that help?