Creating a Table of Dates

Last week, I posted an article on creating a table of numbers. Today, I am continuing by creating a table of dates. Like before, this is a table that we expect to build and write to once, and then read from many thousands of times.

This script has the following assumptions.

The schema ref already exists.
The table ref.numbers already exists.
US English is the only language and culture that you care about. If you need to support other languages and cultures, I recommend that you have a second table foreign keyed to this dates table that includes the culture info.

	-- Drop the ref.dates table if it already exists.
	IF OBJECT_ID('ref.dates') IS NOT NULL DROP TABLE ref.dates;
	GO


	-- Create a temp table with computed values. We will delete this
	-- temp table at the end of this script.
	CREATE TABLE #dates (
	date DATE NOT NULL,
	year AS DATEPART(YEAR, date),
	month AS DATEPART(MONTH, date),
	day AS DATEPART(DAY, date),
	day_of_week AS DATEPART(WEEKDAY, date),
	day_of_year AS DATEPART(DAYOFYEAR, date),
	week_of_year AS DATEPART(WEEK, date),
	quarter AS DATEPART(QUARTER, date)
	);
	GO


	CREATE TABLE ref.dates (
	[date] DATE NOT NULL,
	[year] INT NOT NULL,
	[month] INT NOT NULL,
	[day] INT NOT NULL,
	[day_suffix] CHAR(2) NOT NULL,
	[day_of_week] INT NOT NULL,
	[day_of_year] INT NOT NULL,
	[week_of_year] INT NOT NULL,
	[first_of_month] DATE NOT NULL,
	[last_of_month] DATE NOT NULL,
	[first_of_year] DATE NOT NULL,
	[last_of_year] DATE NOT NULL,
	[quarter] INT NOT NULL,
	[quarter_suffix] CHAR(2) NOT NULL,
	[month_name] VARCHAR(20) NOT NULL,
	[month_name_abbr] VARCHAR(3) NOT NULL,
	[day_name] VARCHAR(20) NOT NULL,
	[day_name_abbr] VARCHAR(3) NOT NULL,
	CONSTRAINT [PK_ref.dates] PRIMARY KEY CLUSTERED ([date])
	);
	GO


	-- year + month + day must be unique.
	CREATE UNIQUE NONCLUSTERED INDEX [UX_ref.dates_year_month_day] ON ref.dates (year, month, day);
	GO


	-- year + day_of_year must be unique.
	CREATE UNIQUE NONCLUSTERED INDEX [UX_ref.dates_year_day_of_year] ON ref.dates (year, day_of_year);
	GO


	-- Set the start date and the number of years. Going as far back as 1900, and running for
	-- 300 years ought to cover every possible scenario.
	DECLARE @start_date DATE = CONVERT(DATE, '1900-01-01');
	DECLARE @number_of_years INT = 300;

	-- Compute the end date. Add the number of years, then back up one day.
	DECLARE @end_date DATE = DATEADD(YEAR, @number_of_years, @start_date);
	SET @end_date = DATEADD(DAY, -1, @end_date);

	-- Insert rows into the temp table.
	WITH cte_date (date) AS
	(
	SELECT DATEADD(DAY, rownum - 1, @start_date)
	FROM
	(
	SELECT TOP (DATEDIFF(DAY, @start_date, @end_date)) rownum = ROW_NUMBER() OVER (ORDER BY number)
	FROM ref.numbers
	) AS dates
	)
	INSERT INTO #dates (date)
	SELECT date
	FROM cte_date;
	GO


	INSERT INTO ref.dates (
	[date],
	year,
	month,
	day,
	day_suffix,
	day_of_week,
	day_of_year,
	week_of_year,
	first_of_month,
	last_of_month,
	first_of_year,
	last_of_year,
	quarter,
	quarter_suffix,
	month_name,
	month_name_abbr,
	day_name,
	day_name_abbr
	)
	SELECT date,
	year,
	month,
	day,
	ref.fn_numeric_suffix(day),
	day_of_week,
	day_of_year,
	week_of_year,
	MIN(date) OVER (PARTITION BY year, month),
	MAX(date) OVER (PARTITION BY year, month),
	MIN(date) OVER (PARTITION BY year),
	MAX(date) OVER (PARTITION BY year),
	quarter,
	ref.fn_numeric_suffix(quarter),
	DATENAME(MONTH, date),
	LEFT(DATENAME(MONTH, date), 3),
	DATENAME(WEEKDAY, date),
	LEFT(DATENAME(WEEKDAY, date), 3)
	FROM #dates
	ORDER BY date ASC;
	GO


	-- Drop the temporary table. We no longer need it.
	DROP TABLE #dates;
	GO


	SELECT * FROM ref.dates;
	GO

view raw create_dates.sql hosted with ❤ by GitHub

What other columns would you include in your dates table?