In case it's of use, I used this script to build the data dimension in a data warehouse. Bank Holidays are based on UK holidays though. The UDFs dbo.WorkingDay and the functions it uses for Easter & holiday dates are elsewhere on the site (I've posted them before) so you can find them by using Search or looking at my posting history.
IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] = 'DX_Date' AND [type] = 'U')
DROP TABLE dbo.DX_Date
GO
CREATE TABLE dbo.DX_Date
( DateKey int IDENTITY(1,1) NOT NULL,
SQLDate smalldatetime NOT NULL,
DayOfWeek tinyint NOT NULL,
DayOfWeekName varchar(9) NOT NULL,
DayOfMonth tinyint NOT NULL,
DayOfYear smallint NOT NULL,
LastDayOfWeek_B bit NOT NULL,
LastDayOfMonth_B bit NOT NULL,
WeekEndingDate char(8) NOT NULL,
WeekNumber tinyint NOT NULL,
MonthName varchar(9) NOT NULL,
MonthNumber tinyint NOT NULL,
YearMonth char(6) NOT NULL,
Quarter tinyint NOT NULL,
YearQuarter char(6) NOT NULL,
Year smallint NOT NULL,
FinDayOfYear smallint NOT NULL,
FinWeekNumber tinyint NOT NULL,
FinPeriod tinyint NOT NULL,
FinYearPeriod char(6) NOT NULL,
FinQuarter tinyint NOT NULL,
FinYearQuarter char(6) NOT NULL,
FinYear smallint NOT NULL,
BankHoliday_B bit NOT NULL,
Weekday_B bit NOT NULL,
CONSTRAINT DX_Date_PK PRIMARY KEY (DateKey)
)
GO
-- Populate table
CREATE TABLE #numbers
( n int IDENTITY(1,1) PRIMARY KEY CLUSTERED )
WHILE ISNULL(SCOPE_IDENTITY(),0) <= 100000
BEGIN
INSERT #numbers DEFAULT VALUES
END
GO
IF @@DATEFIRST != 7
PRINT 'WARNING: @@DATEFIRST != 7'
DECLARE @dtStartDate datetime, @dtEndDate datetime, @iDays int
SET @dtStartDate = CONVERT(datetime, '19991231')
SET @dtEndDate = CONVERT(datetime, '20101231')
SET @iDays = DATEDIFF(dd, @dtStartDate, @dtEndDate)
INSERT DX_Date (SQLDate, DayOfWeek, DayOfWeekName, DayOfMonth, DayOfYear, LastDayOfWeek_B, LastDayOfMonth_B, WeekEndingDate,
WeekNumber, MonthName, MonthNumber, YearMonth, Quarter, YearQuarter, Year, FinDayOfYear, FinWeekNumber, FinPeriod,
FinYearPeriod, FinQuarter, FinYearQuarter, FinYear, BankHoliday_B, Weekday_B)
SELECTDATEADD(dd, n, @dtStartDate),
DATEPART(dw, DATEADD(dd, n, @dtStartDate)),
DATENAME(dw, DATEADD(dd, n, @dtStartDate)),
DATEPART(dd, DATEADD(dd, n, @dtStartDate)),
DATEPART(dy, DATEADD(dd, n, @dtStartDate)),
CASE WHEN DATEPART(dw, DATEADD(dd, n, @dtStartDate)) = 1 THEN 1 ELSE 0 END,
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) = DATEPART(mm, DATEADD(dd, n + 1, @dtStartDate)) THEN 0 ELSE 1 END,
CONVERT(varchar, DATEADD(dd, n + ((8 - DATEPART(dw, DATEADD(dd, n, @dtStartDate))) % 7), @dtStartDate), 112),
DATEPART(wk, DATEADD(dd, n, @dtStartDate)),
DATENAME(mm, DATEADD(dd, n, @dtStartDate)),
DATEPART(mm, DATEADD(dd, n, @dtStartDate)),
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate))) +
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) < 10
THEN '0' + CONVERT(varchar, DATEPART(mm, DATEADD(dd, n, @dtStartDate)))
ELSE CONVERT(varchar, DATEPART(mm, DATEADD(dd, n, @dtStartDate))) END,
DATEPART(qq, DATEADD(dd, n, @dtStartDate)),
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate))) + '0' + CONVERT(varchar, DATEPART(qq, DATEADD(dd, n, @dtStartDate))),
DATEPART(yy, DATEADD(dd, n, @dtStartDate)),
DATEDIFF(dd, CONVERT(datetime,
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate)) -
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) <= 4 THEN 1 ELSE 0 END) + '0501'),
DATEADD(dd, n, @dtStartDate)) + 1,
DATEDIFF(ww, CONVERT(datetime,
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate)) -
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) <= 4 THEN 1 ELSE 0 END) + '0501'),
DATEADD(dd, n, @dtStartDate)) + 1,
(DATEPART(mm, DATEADD(dd, n, @dtStartDate)) + 8) % 12,
'XXXXXX',
0,
'XXXXXX',
DATEPART(yy, DATEADD(dd, n, @dtStartDate)) -
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) <= 4 THEN 1 ELSE 0 END,
CASE WHEN eBIS_INTERACTIVE.dbo.WorkingDay(DATEADD(dd, n, @dtStartDate)) = 2 THEN 0 ELSE 1 END,
CASE WHEN eBIS_INTERACTIVE.dbo.WorkingDay(DATEADD(dd, n, @dtStartDate)) = 1 THEN 0 ELSE 1 END
FROM #numbers
WHERE n <= @iDays
GO
UPDATE DX_Date SET FinPeriod = 12 WHERE FinPeriod = 0
GO
UPDATE DX_Date
SET FinYearPeriod = CONVERT(varchar, FinYear) + CASE WHEN FinPeriod < 10 THEN '0' ELSE '' END + CONVERT(varchar, FinPeriod),
FinQuarter = CASE WHEN FinPeriod IN (1,2,3) THEN 1
WHEN FinPeriod IN (4,5,6) THEN 2
WHEN FinPeriod IN (7,8,9) THEN 3
WHEN FinPeriod IN (10,11,12) THEN 4 END
GO
UPDATE DX_Date
SETFinYearQuarter = CONVERT(varchar, FinYear) + '0' + CONVERT(varchar, FinQuarter)
GO
UPDATE DX_Date
SETFinWeekNumber = 1
WHERE FinWeekNumber = 53
GO
-- Create index
CREATE NONCLUSTERED INDEX DX_Date_IX1
ON DX_Date (SQLDate)
WITH FILLFACTOR = 100
GO
DROP TABLE #numbers
GO