DECLARE @startDate AS date='1/10/2010',
		@endDate AS date='6/1/2011'

SELECT @startDate=DATEADD(WEEK,-1,@startDate) -- lazy logic to handle the  first week_start...


;WITH 
tally(n) AS 
(	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 
	FROM master.dbo.spt_values),
allDates AS 
(	SELECT	n, DATEADD(DAY,n,@startDate) as [date]
	FROM tally
	WHERE n<=DATEDIFF(DAY,@startdate,@endDate)),
FilteredCalendar AS
(	SELECT	n as spoon,
			[date] AS Media_Week_Start_Date, 
			DATEPART(MONTH,([date])) AS Media_Month,
			DATEPART(isowk,([date])) AS Media_Week,
			DATENAME(M,([date])) AS Media_Month_Name,
			DATEPART(QUARTER,[date]) AS Media_Quarter,
			DATEPART(YEAR,[date]) AS Media_Year
	FROM allDates),
top1 AS
(	SELECT TOP 2000000 RANK() OVER (PARTITION BY Media_Week ORDER BY Media_Week_Start_Date) AS top1,* 
	FROM FilteredCalendar
	ORDER BY Media_Week_Start_Date)
SELECT	--top1, n,  --uncomment to tinker with the logic
		Media_Week_Start_Date, Media_Month, Media_Week, Media_Month_Name, Media_Quarter, Media_Year
FROM top1
WHERE top1=1 AND spoon>0
ORDER BY spoon

Media_Week_Start_Date	Media_Week
2010-01-04				1
2010-01-11				2
2010-01-18				3
2010-01-25				4
2010-02-01				5
2010-02-08				6
2010-02-15				7
......
2010-12-06				49
2010-12-13				50
2010-12-20				51
2010-12-27				52

DECLARE @TodayIs date=GETDATE(),	--a date
		@n int=3;				--a number

-- A quick DATEADD demo
SELECT	@todayIS AS '@today', 
		@n AS '@n',
		DATEADD(DAY,@n,@TodayIs) AS '@today + @n';

--What set based programming looks like
WITH 
UnorderedSet(n)	AS 
(	SELECT 1 UNION ALL 
	SELECT 1 UNION ALL 
	SELECT 1),	
tallyTable(n) AS 
(	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) --The fastest way to count to {whatever} in SQL
	FROM UnorderedSet) 
SELECT	n AS [n wearing nothing],
		DATEADD(DAY,n,GETDATE()) AS [n dressed in (today's date) + (n)days]
FROM tallyTable;

CREATE TABLE BroadcastCalendar (
 [dated] [datetime] NOT NULL PRIMARY KEY
,[Media_Week] [bigint] NOT NULL
,[Media_Month] [int] NOT NULL
,[Media_MonthName] [nvarchar](30) NOT NULL
,[Media_Quarter] [int] NOT NULL
,[Media_Year] [int] NOT NULL
);

with 
  n10(N) as (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1)
 ,n100(N) as (SELECT 1 FROM n10 as a,n10 as b)
 ,Tally(N) as (SELECT ROW_NUMBER() OVER(ORDER BY @@SPID) FROM n100 as a,n100 as b)
 ,cal as
 (
 select 
  a.dated,
  datepart(dy,a.dated) as DayOfYear,
  datepart(day,a.dated) as DayOfMonth,
  datename(dw,a.dated) as [DayOfWeek],
  datepart(year,a.dated) as CalendarYear,
  1+(ROW_NUMBER() OVER(ORDER BY T1.N)-1)/7 as WeekId
 from Tally T1
 cross apply (select dateadd(day,T1.N,'20091227')) a(dated) -- 28th Dec 2009 is a Monday
 )
 ,weeks as
 (
 select 
  WeekId,
  Max(CalendarYear) as CalYear,
  min(dated) as WeekStart,
  CASE WHEN MIN(DayOfMonth) = 1 THEN DATEPART(MONTH,MAX(dated)) ELSE DATEPART(MONTH,MIN(dated)) END AS CalMonth,
  CASE WHEN MIN(DayOfMonth) = 1 THEN DATENAME(MONTH,MAX(dated)) ELSE DATENAME(MONTH,MIN(dated)) END AS CalMonthName
 from cal
 group by WeekId
 )
INSERT BroadcastCalendar([dated], [Media_Week], [Media_Month], [Media_MonthName], [Media_Quarter], [Media_Year])
select 
 newDates.dated,
 1+(ROW_NUMBER() OVER(PARTITION BY CalYear ORDER BY WeekId)-1)/7 as Media_Week,
 CalMonth as Media_Month,
 CalMonthName as Media_MonthName,
 1+(CalMonth-1)/3 as Media_Quarter,
 CalYear as Media_Year
from weeks
cross apply (
 select DATEADD(day,N-1,weeks.WeekStart)
 FROM Tally
 WHERE N>=1 AND N<=7
 ) newDates(dated)
order by WeekId;

CREATE FUNCTION getBroadcastCalendar(@dated DATE)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN(SELECT [Media_Week], [Media_Month], [Media_MonthName], [Media_Quarter], [Media_Year] FROM dbo.BroadcastCalendar WHERE dated=@dated)

SELECT [Media_Week], [Media_Month], [Media_MonthName], [Media_Quarter], [Media_Year] 
FROM dbo.getBroadcastCalendar(getdate());

┌────────────┬─────────────┬─────────────────┬───────────────┬────────────┐
│ Media_Week │ Media_Month │ Media_MonthName │ Media_Quarter │ Media_Year │
├────────────┼─────────────┼─────────────────┼───────────────┼────────────┤
│ 15         │ 4           │ April           │ 2             │ 2013       │
└────────────┴─────────────┴─────────────────┴───────────────┴────────────┘