Here's some code to generate a Broadcast Calendar table:

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;

That will take you up to 2037 roughly...

Then this iTVF will be your function: (Assuming you still want one for some reason- why not just use the table?)

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)

Called like this:

SELECT [Media_Week], [Media_Month], [Media_MonthName], [Media_Quarter], [Media_Year]

FROM dbo.getBroadcastCalendar(getdate());

+-------------------------------------------------------------------------+

¦[highlight="#808080"] Media_Week [/highlight]¦[highlight="#808080"] Media_Month [/highlight]¦[highlight="#808080"] Media_MonthName [/highlight]¦[highlight="#808080"] Media_Quarter [/highlight]¦[highlight="#808080"] Media_Year [/highlight]¦

+------------+-------------+-----------------+---------------+------------¦

¦ 15 ¦ 4 ¦ April ¦ 2 ¦ 2013 ¦

+-------------------------------------------------------------------------+

EDIT: added qualifier about not needing the function (it was late)