I tend to have a generic calendar table build of which this is a cut down version.

This is the one I tend to use as a prepopulation staging table for a Dim.Calendar, all I need to do is insert the Fulldate and everything is worked out for me.

CREATE TABLE #Calendar --drop table #dates_to_add

(

FullDate DATETIME

,YearMonthDay AS Convert(varchar(8),FullDate,112)

,YearMonth AS Convert(varchar(6),FullDate,112)

,RetentionYear INT

,DayWeekNo AS DatePart(dw,FullDate)

,DayWeekCode AS CAST(LEFT(DATENAME(weekday,FullDate),3) as Char(3))

,DayWeekDesc AS CAST(DATENAME(weekday,FullDate) as Varchar(10))

,DayMonthNo AS CAST(DAY(FullDate) as SMALLINT)

,DayYearNo AS Cast(DatePart(dy,FullDate) AS SMALLINT)

,WeekNo AS CAST(DatePart(wk,FullDate) AS TINYINT)

,MonthNo AS CAST(MONTH(FullDate) AS TINYINT)

,MonthCode AS CAST(DATENAME(Month,FullDate) AS CHAR(3))

,MonthDesc AS CAST(DATENAME(Month,FullDate) AS VARCHAR(10))

,RetentionMonthNo INT

,QuarterNo AS CAST(DATEPART(qq,FullDate) as TINYINT)

,YearNo AS CAST(Year(FullDate) AS INT)

,IsMonthEnd AS CAST(CASE

WHEN ( [FullDate] < '9999-11-30 00:00:00.000' ) -- avoid overflow with poor data in PHX

THEN

CASE CONVERT(DATE, [FullDate])

WHEN CONVERT(DATE, DATEADD(S,-1,DATEADD(mm, DATEDIFF(m,0,[FullDate])+1,0))) THEN 1

ELSE 0

END

ELSE 0

END AS BIT)

,IsQuarterEnd AS CAST(CASE CONVERT(DATE, [FullDate])

WHEN CONVERT(DATE, DATEADD(qq,DATEDIFF(qq,-1, [FullDate]),-1)) THEN 1

ELSE 0

END AS BIT)

,IsYearEnd AS CAST(CASE CONVERT(DATE, [FullDate])

WHEN DATEADD(yy,DATEDIFF(yy,-1,[FullDate]),-1) THEN 1

ELSE 0

END AS BIT)

,IsWeekend AS CAST(CASE WHEN DateName(DW,FullDate) IN ('Saturday','Sunday') THEN 1 ELSE 0 END AS BIT)

,IsHoliday BIT DEFAULT (0)

,AggregationType VARCHAR(20) DEFAULT ('Period')

)

;

The Insert into this is simply

Insert into #Calendar

Select FullDate

From atable

--Or

Insert into #Calendar

Select DateAdd(d,N,@Base)

From Tally

Then loading the dim.Calendar from this table as part of the ETL.

You could create this as a fixed table with all the calculated columns included but there may be a performance hit.