First of all, I recommend creating a permanent calendar table. A very simplified example would be something like this:

---------------------------------------------------------------------

---------------------- Create a calendar table ----------------------

---------------------------------------------------------------------

create table calendar (

date_key date primary key,

year smallint,

quarter smallint,

month smallint,

day_of_year smallint,

day smallint,

week_of_year smallint,

day_of_week smallint,

day_of_week_label nvarchar(50)

);

insert into calendar

with dk as (

select dateadd(d, rank() over(order by newid()), '19990101') as date_key

from sys.objects cross join sys.columns

)

selectdate_key,

datepart(yy, date_key),

datepart(qq, date_key),

datepart(m, date_key),

datepart(dy, date_key),

datepart(d, date_key),

datepart(wk, date_key),

datepart(dw, date_key),

datename(dw, date_key)

from dk

Materialising into a table allows easy implementation of different calendaring rules, such as 4-4-5, 4-5-4, ISO, etc.

Further reading: http://www.sqlservercentral.com/articles/T-SQL/70482/[/url]