Best approach for populating date dimension table

I am looking to populate a date dimension table in a SQL Server 2008 database. The fields in the table are as follows: [DateId] INT IDENTITY(1,1) PRIMARY KEY [DateTime] DATETIME [Date] DATE [DayOfWeek_Number] TINYINT [DayOfWeek_Name] VARCHAR(9) [DayOfWeek_ShortName] VARCHAR(3) [Week_Number] TINYINT [Fiscal_DayOfMonth] TINYINT [Fiscal_Month_Number] TINYINT [Fiscal_Month_Name] VARCHAR(12) [Fiscal_Month_ShortName] VARCHAR(3) [Fiscal_Quarter] TINYINT [Fiscal_Year] INT [Calendar_DayOfMonth] TINYINT [Calendar_Month Number] TINYINT [Calendar_Month_Name] VARCHAR(9) [Calendar_Month_ShortName] VARCHAR(3) [Calendar_Quarter] TINYINT [Calendar_Year] INT [IsLeapYear] BIT [IsWeekDay] BIT [IsWeekend] BIT [IsWorkday] BIT [IsHoliday] BIT [HolidayName] VARCHAR(255) I have written a function DateListInRange(D1,D2) that returns all the dates between two parameter dates D1 and D2 inclusive. ie. parameters '2014-01-01' and '2014-01-03' would return: 2014-01-01 2014-01-02 2014-01-03 I want to populate the DATE_DIM table for all dates within a range, i.e. 2010-01-01 to 2020-01-01. Most of the fields can be populated with the SQL 2008 DATEPART, DATENAME, and YEAR functions. The fiscal data contains slightly more logic, some of which is dependant on each other. For example: Fiscal quarter 1 -> Fiscal month must be 1, 2 or 3 Fiscal quarter 2 -> Fiscal month must be 4, 5 or 6 I can easily write a table valued function that accepts a specific date, and then outputs all of the fiscal data, or ALL of the fields even. Then I would just need this function to run on each row of the DateListInRange function. I am not highly concerned with speed as this will only need to be populated a few times a year when the table of holidays is altered. What is the best way to write this in SQL? Currently its like this: SELECT [Date], CAST([Date] AS DATE) AS [Date], DATEPART(W,[Date]) AS [DayOfWeek_Number], -- First day of week is sunday DATENAME(W,[Date]) AS [DayOfWeek_Name], SUBSTRING(DATENAME(DW,[Date]),1,3) AS [DayOfWeek_ShortName], DATEPART(WK, [Date]) AS [WeekNumber], DATEPART(M, [Date]) AS [Calendar_Month_Number], DATENAME(M, [Date]) AS [Calendar_Month_Name], SUBSTRING(DATENAME(M, [Date]),1,3) AS [Calendar_Month_ShortName], DATEPART(QQ, [Date]) AS [Calendar_Quarter], YEAR([Date]) AS [Calendar_Year], CASE WHEN ( (YEAR([Date]) % 4 = 0) AND (YEAR([Date]) % 100 != 0) OR (YEAR([Date]) % 400 = 0) ) THEN 1 ELSE 0 END AS [IsLeapYear], CASE WHEN ( DATEPART(W,[Date]) = 1 OR DATEPART(W,[Date]) = 7 ) THEN 0 ELSE 1 END AS [IsWeekDay] FROM [DateListForRange] ('2014-01-01','2014-01-31') If I do the same for the fiscal data there will be quite a bit of repetition in each case statement would could be avoided using a function and maybe cross applying the TVF over the list of dates. Please note I am using SQL Server 2008 so a lot of newer date functionality is minimal.