I needed to generate a range of data about dates for a lookup table. There’s an elegant solution using a recursive cte that does the job;

WITH daysCte
(
	d
)
AS
(
	SELECT CONVERT(DATETIME, '1 January 2011') AS d -- starting date
	UNION ALL
	SELECT DATEADD(D, 1, d)
	FROM daysCte
	WHERE DATEPART(yyyy, d) <= 2012 -- stop year
)
SELECT d,
	   DATEPART(wk, d) AS week_number,
	   DATENAME(dw, d) AS day_name,
	   DATENAME(m, d) AS month_name,
	   DATENAME(q, d) AS [quarter]
FROM daysCte
OPTION (MAXRECURSION 800); -- set > number of days you want data for

This will display something looking like below;

d week_number	day_name	month_name	quarter
2011-01-01 00:00:00.000	1 Saturday	January 1
2011-01-02 00:00:00.000	2 Sunday January 1
2011-01-03 00:00:00.000	2 Monday January 1
2011-01-04 00:00:00.000	2 Tuesday January 1
2011-01-05 00:00:00.000	2 Wednesday	January 1
2011-01-06 00:00:00.000	2 Thursday	January 1
2011-01-07 00:00:00.000	2 Friday January 1