You can use DATEPART to determine what week the current date is. Below is a code that demonstrates it.
DECLARE @currentDayOfWeek AS INT
DECLARE @currentDate AS DATETIME
DECLARE @startDate AS DATETIME
SET @currentDayOfWeek = (DATEPART(dw, GETDATE()) - 1) * -1
SET @currentDate = CAST(CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/'
+ CAST(DAY(GETDATE()) AS VARCHAR(2)) + '/'
+CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS DATETIME)
SET @startDate = DATEADD(DW,@currentDayOfWeek,@currentDate)
SELECT YEAR(@currentDate) AS [YEAR],
DATEPART(WW, @currentDate) AS WEEK,
@startDate AS STARTDATE,
DATEADD(DW, 6, @startDate) AS ENDDATE
Output is
YEAR WEEK STARTDATE ENDDATE
2011 13 03/20/2011 03/26/2011
Note that you week is 13 because it computes for the week number for the whole year. You need to do a little subtraction if you want it to do the week numbers by month and not by year.
Not an elegant solution but it can give you a headstart on what you need to do. :)
Submit an article or tip