How to generate week ranges for a year in SQL server 2005?

I have to insert YEAR WEEKNUMBER STARTDATE ENDDATE values to a datatable (say weekrange), if I pass 2011 as year.

Week range starts at 2011-03-28 to 2011-04-03 (because in my database 2010 last week range ends with 2011-03-27)

Like this I have to generate for 52 we开发者_如何转开发eks.

I want to write a stored procedure, that takes only year as parameter. with this year I have to generate week ranges and insert into my table as shown above.

How can I do this ?

Regards, jn

The following will generate the data you need based on SQL server's week number definition (see the notes on DATEPART(ww,...) here). Note that this will mean some years have 53 weeks.

DECLARE @year AS CHAR(4)
SET @year = '2011'

DECLARE @firstDay DATETIME
SET @firstDay = CAST(@year + '0101' AS DATETIME)

;WITH dayCTE 
AS 
(
    SELECT DATEADD(dd,ROW_NUMBER() OVER (ORDER BY name) - 1, @firstDay) AS yearday
    FROM    master.dbo.spt_values
)
,weekCTE
AS
(
    SELECT  yearday, 
            DATEPART(WW,yearday) AS weekno
    FROM dayCTE
    WHERE YEAR(yearday) = @year
)
SELECT  @year        AS [YEAR],
        weekno       AS WEEKNUMBER, 
        MIN(yearday) AS STARTDATE, 
        MAX(yearday) AS ENDDATE
FROM weekCTE
GROUP BY weekno
ORDER BY weekno

It's a side point, but I'd recommend renaming the YEAR column of your target table to something which isn't a T-SQL keyword.