http://www.sqlservercentral.com/articles/T-SQL/76599/
create view [dbo].[Calendar] as with Dates as ( select cast(’2010-01-01′ as date) as CalendarDate union all select dateadd(day , 1, CalendarDate) AS CalendarDate from Dates where dateadd (day, 1, CalendarDate)<= dateadd(year, 1, getdate()) ) select CalendarDate, CalendarYear=year(CalendarDate), CalendarQuarter=datename(quarter, CalendarDate), CalendarMonth=month(CalendarDate), CalendarWeek=datepart(wk, CalendarDate), CalendarDay=day(CalendarDate), CalendarMonthName=datename(month, CalendarDate), CalendarDayOfYear=datename(dayofyear, CalendarDate), Weekday=datename(weekday, CalendarDate), DayOfWeek=datepart(weekday, CalendarDate) from Dates
Here is an example that queries the last seven days of snapshot using a simple join to CalendarPeriod.
