Add statements at the top to select your database:
USE [DATABASE] GO
Save and run the script in the query analyzer. I have used this in SQL Server 2008, 2008R2 and 2012. I have no reason to believe it won't work in 2005 or even 2000, but I have not tested it in those environments.
Running on a Windows 7 Developer box against a 2008 R2 server it took about 43 sections to generate a 25-year table.
/*
This script is freely shared by the author, Joseph M. Morgan, Principal Programmer/Analyst I at Amerigroup, a division of Wellpoint. Permission is granted to copy, share, modify and distribute it without restriction except for commercial gain, in which case send me some of the money!
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON;
IF NOT EXISTS ( SELECT
*
FROM
sys.objects
WHERE
object_id = OBJECT_ID(N'[dbo].[DATE_DIM]')
AND type IN (N'U') )
BEGIN
CREATE TABLE [dbo].[DATE_DIM]
(
[DATE_ID] [bigint] IDENTITY(1, 1)
NOT NULL
,[SQL_DATE] [datetime] NOT NULL
,[DAY] [smallint] NOT NULL
,[DAY_OF_WEEK] [smallint] NULL
,[WEEK] [smallint] NOT NULL
,[MONTH] [SMALLINT] NOT NULL
,[QUARTER] [SMALLINT] NOT NULL
,[YEAR] [SMALLINT] NOT NULL
,[DAY_OF_YEAR] [SMALLINT] NOT NULL
,[DAY_TEXT] [VARCHAR](50) NOT NULL
,[MONTH_TEXT] [VARCHAR](50) NOT NULL
,[QUARTER_TEXT] [VARCHAR](50) NOT NULL
,[DAY_TEXT_ABBREV] [VARCHAR](3) NULL
,[MONTH_TEXT_ABBREV] [VARCHAR](3) NULL
,[LDM] [BIGINT] NULL
,[LDQ] [BIGINT] NULL
,[LDY] [BIGINT] NULL
,[IS_BUS_DAY] [BIT] NULL
,[IS_WEEKDAY] [BIT] NULL
,[IS_HOLIDAY] [BIT] NULL
,[RPT_HDR_LONG] AS CONVERT(VARCHAR(25), (([MONTH_TEXT] + ' ')
+ CONVERT([VARCHAR](4), [YEAR], (0))))
PERSISTED
,[RPT_HDR_SHORT] AS CONVERT(VARCHAR(25), (([MONTH_TEXT_ABBREV]
+ ' ')
+ CONVERT([VARCHAR](4), [YEAR], (0))))
PERSISTED
,CONSTRAINT [PK_Date_dbo] PRIMARY KEY CLUSTERED
([DATE_ID] ASC)
)
CREATE NONCLUSTERED INDEX [IX_Dates] ON [dbo].[DATE_DIM]
(
[SQL_DATE] ASC
)
CREATE NONCLUSTERED INDEX [IX_Dates_1] ON [dbo].[DATE_DIM]
(
[Year] ASC
)
CREATE NONCLUSTERED INDEX [IX_Dates_2] ON [dbo].[DATE_DIM]
(
[Month] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85)
/****** Object: Index [IX_Dates_3] Script Date: 11/30/2012 10:20:53 AM ******/ CREATE NONCLUSTERED INDEX [IX_Dates_3] ON [dbo].[DATE_DIM]
(
[Day] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85)
/****** Object: Index [IX_Dates_4] Script Date: 11/30/2012 10:20:53 AM ******/ CREATE NONCLUSTERED INDEX [IX_Dates_4] ON [dbo].[DATE_DIM]
(
[LDM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85)
END
ELSE
BEGIN
-- If it already existed, it might have data in it, so we truncate just in case.
TRUNCATE TABLE dbo.DATE_DIM
END
GO
/* ====================== Create the three functions used in the code. ======================
(We avoided errors by creating the table the functions reference first.)*/
IF OBJECT_ID(N'dbo.svfn_AMS_LDM') IS NOT NULL
DROP FUNCTION dbo.svfn_AMS_LDM
GO
CREATE FUNCTION [dbo].[svfn_AMS_LDM]
(
@MONTH SMALLINT
,@YEAR SMALLINT
)
RETURNS BIGINT
AS
BEGIN
DECLARE @RESULT BIGINT
SET @RESULT = (SELECT
MAX(DATE_ID)
FROM
DBO.DATE_DIM
WHERE
[MONTH] = @MONTH
AND [YEAR] = @YEAR
)
RETURN @RESULT
END
GO
IF OBJECT_ID(N'dbo.svfn_AMS_LDQ') IS NOT NULL
DROP FUNCTION dbo.svfn_AMS_LDQ
GO
CREATE FUNCTION [dbo].[svfn_AMS_LDQ]
(
@QUARTER SMALLINT
,@YEAR SMALLINT
)
RETURNS BIGINT
AS
BEGIN
DECLARE @RESULT BIGINT
SET @RESULT = (SELECT
MAX(DATE_ID)
FROM
DATE_DIM
WHERE
[QUARTER] = @QUARTER
AND [YEAR] = @YEAR
)
RETURN @RESULT
END
GO
IF OBJECT_ID(N'dbo.svfn_AMS_LDY') IS NOT NULL
DROP FUNCTION dbo.svfn_AMS_LDY
GO
CREATE FUNCTION [dbo].[svfn_AMS_LDY] (@YEAR SMALLINT)
RETURNS BIGINT
AS
BEGIN
DECLARE @RESULT BIGINT
SET @RESULT = (SELECT
MAX(DATE_ID)
FROM
DATE_DIM
WHERE
[YEAR] = @YEAR
)
RETURN @RESULT
END
GO
/*====================== Now populate the table =============================== */
BEGIN TRY
-- Declare and set the date, which is the starting date that will be incremented, and the end date desired
DECLARE @Date DATETIME
DECLARE @EndDate DATETIME
SET @Date = CONVERT(DATETIME, '01-01-2000')
SET @EndDate = CONVERT(DATETIME, '12-31-2025')
WHILE @Date <= @EndDate
BEGIN
INSERT INTO [dbo].[DATE_DIM]
(SQL_DATE
,[DAY]
,[DAY_OF_WEEK]
,[WEEK]
,[MONTH]
,[QUARTER]
,[YEAR]
,[DAY_OF_YEAR]
,[DAY_TEXT]
,[MONTH_TEXT]
,[QUARTER_TEXT]
,[DAY_TEXT_ABBREV]
,[MONTH_TEXT_ABBREV]
,[IS_BUS_DAY]
,[IS_WEEKDAY]
,[IS_HOLIDAY]
)
SELECT
@DATE AS SQLDATE
,DATEPART(D, @DATE) AS [DAY]
,DATEPART(DW, @DATE) AS [DAY_OF_WEEK]
,DATEPART(WK, @DATE) AS [WEEK]
,DATEPART(M, @DATE) AS [MONTH]
,DATEPART(Q, @DATE) AS [QUARTER]
,DATEPART(YYYY, @DATE) AS [YEAR]
,DATEPART(DY, @DATE) AS [DAY_OF_YEAR]
,DATENAME(DW, @DATE) AS DAY_TEXT
,DATENAME(M, @DATE) AS MONTH_TEXT
,'Q' + CONVERT(CHAR(1), DATENAME(QQ, @DATE)) AS QUARTERTEXT
,SUBSTRING(DATENAME(DW, @DATE), 1, 3) AS DAY_TEXTABBREVIATION
,SUBSTRING(DATENAME(M, @DATE), 1, 3) AS MONTH_TEXTABBREVIATION
,IS_BUS_DAY = CASE DATEPART(DW, @DATE)
WHEN 7 THEN 0
WHEN 1 THEN 0
ELSE 1
END
,ISWEEKDAY = CASE DATEPART(DW, @DATE)
WHEN 7 THEN 0
WHEN 1 THEN 0
ELSE 1
END
,0 AS IS_HOLIDAY
SET @DATE = DATEADD(D, 1, @DATE)
END
/* ====================== Add holidays to the table ========================
First set the default U.S. Federal Holidays to be holidays, not business days.
In some cases the dates are fixed, in some they are changeable. If your company treats
any of these as business days, just comment out the relevant section(s) */
UPDATE
[dbo].[DATE_DIM]
SET
IS_BUS_DAY = 0
,IS_HOLIDAY = 1
WHERE
(
--New [Year]'s Day (Jan 1)
[Month] = 1
AND DAY = 1
OR
--Christmas Day (Dec 25)
[Month] = 12
AND DAY = 25
OR
--Independence Day (Jul 4)
[Month] = 7
AND DAY = 4
OR
--Veteran's Day (Nov 11)
[Month] = 11
AND Day = 11
OR
--Thanksgiving (4th Thursday)
([Month] = 11
AND [DAY_OF_WEEK] = 5
AND DATE_ID IN (SELECT
MIN(DATE_ID) + 21
FROM
DATE_DIM
WHERE
[Month] = 11
AND [DAY_OF_WEEK] = 5
GROUP BY
[Year])
)
OR
--Memorial Day (last Monday)
([Month] = 5
AND [DAY_OF_WEEK] = 2
AND DATE_ID IN (SELECT
MAX(DATE_ID)
FROM
DATE_DIM
WHERE
[Month] = 5
AND [DAY_OF_WEEK] = 2
GROUP BY
[Year])
)
OR
-- Labor Day (1st Monday)
([Month] = 9
AND [DAY_OF_WEEK] = 2
AND DATE_ID IN (SELECT
MIN(DATE_ID)
FROM
DATE_DIM
WHERE
[Month] = 9
AND [DAY_OF_WEEK] = 2
GROUP BY
[Year])
)
OR
--Martin Luther King Day (3rd Monday)
([Month] = 1
AND [DAY_OF_WEEK] = 2
AND DATE_ID IN (SELECT
MIN(DATE_ID) + 14
FROM
DATE_DIM
WHERE
[Month] = 1
AND [DAY_OF_WEEK] = 2
GROUP BY
[Year])
)
-- Columbus Day
OR ([Month] = 10
AND [DAY_OF_WEEK] = 2
AND DATE_ID IN (SELECT
MIN(DATE_ID) + 7
FROM
DATE_DIM
WHERE
[Month] = 10
AND [DAY_OF_WEEK] = 2
GROUP BY
[Year])
)
)
/* ====== Now adjust for the days when the holiday is not a "Monday Holiday", and falls on a weekend. ======
In most companies, Saturday holidays are observed the Friday before and Sunday holidays the Monday after,
but you can adjust this as needed */
--Set Saturday holidays to Friday
UPDATE
[dbo].[DATE_DIM]
SET
IS_HOLIDAY = 1
,IS_BUS_DAY = 0
WHERE
DATE_ID IN (SELECT
DATE_ID - 1
FROM
DATE_DIM
WHERE
IS_HOLIDAY = 1
AND [DAY_OF_WEEK] = 7)
--Set Sunday holidays to Monday
UPDATE
[dbo].[DATE_DIM]
SET
IS_HOLIDAY = 1
,IS_BUS_DAY = 0
WHERE
DATE_ID IN (SELECT
DATE_ID + 1
FROM
DATE_DIM
WHERE
IS_HOLIDAY = 1
AND [DAY_OF_WEEK] = 1)
/* ========================== Add in the end-of-period values ====================
These are especially useful for aggregation and sorting, since you can order by LDM,
for example, and have the order be in the correct calendar order, even across multiple years instead of running datepart calculations for this purpose
*/ UPDATE
[dbo].[DATE_DIM]
SET
LDM = ld.LDM
,LDQ = ld.LDQ
,LDY = ld.LDY
FROM
(SELECT
DATE_ID
,dbo.svfn_AMS_LDM(Month, Year) AS LDM
,dbo.svfn_AMS_LDQ(Quarter, Year) AS LDQ
,dbo.svfn_AMS_LDY(Year) AS LDY
,dates_1.MONTH_TEXT
,dates_1.[MONTH_TEXT_ABBREV]
,dates_1.Year
FROM
[dbo].[DATE_DIM] AS Dates_1
) AS ld
INNER JOIN [dbo].[DATE_DIM]
ON ld.DATE_ID = [dbo].[DATE_DIM].DATE_ID
SELECT
RESULT = 'Success'
END TRY
BEGIN CATCH
SELECT
RESULT = 'Error ' + CONVERT(VARCHAR(2000), @@ERROR) + ' on line '
+ ERROR_LINE() + ': ' + ERROR_MESSAGE()
RETURN
END CATCH
GO 
2019-03-25
62,533 reads
2019-03-25
29,123 reads
Authors A full list of authors on SQLServerCentral is available here. Leaderboard See how your scores for the forums, Question of the Day, and overall score compare on the leaderboard. Blogs The following content is syndicated from SQL Server-related blogs elsewhere on the web.
2019-03-25
3,979 reads
Write for SQLServerCentral - we're looking for new writers for SQL Server articles, scripts and questions.
2019-03-25
9,710 reads
Redgate (which means Red Gate Software Limited and its subsidiary Red Gate Software Inc.) respects your privacy. All information you give us is held with the utmost care and security. Please take time to review this privacy policy as it sets out our privacy practices and tells you how your personal information will be treated […]
2019-03-25
45,645 reads