Try this:
DECLARE @dt DATETIME, @WeekOfMonth TINYINT
SET @dt = '2007-07-08'
SET @WeekOfMonth = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0), @dt) +1
PRINT @WeekOfMonth
Cheers
EDIT:
After better analyzing your question, this should be a proper solution:
DECLARE @dt DATETIME, @WeekOfMonth TINYINT, @FirstDayOfMonth TINYINT, @TodayInWeek TINYINT, @TodaysInMonth TINYINT
SET @dt = GETDATE()
SET @WeekOfMonth = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0), @dt) +1
SET @FirstDayOfMonth = DATEPART(dw,(DATEADD(dd,-(DAY(@dt)-1), @dt)))
SET @TodayInWeek = DATEPART(dw, @dt)
IF @TodayInWeek < @FirstDayOfMonth
BEGIN
SET @TodaysInMonth = @WeekOfMonth - 1
END
ELSE
BEGIN
SET @TodaysInMonth = @WeekOfMonth
END
PRINT @TodaysInMonth
Cheers