Create Date Table

Create Date Table

A great thing to have in any database is a table that stores dates. It can save tons of space to have disparate tables reference one date table that can store meta data regarding each date. About 5000 rows can hold ten years worth of dates.

Fill in the start and end dates and this script will create a date table:

CREATE TABLE dates(
id INT NOT NULL,
[DATE] AS (yyyy+mm+dd),
yyyy CHAR(4) NOT NULL,
mm CHAR(2) NOT NULL,
dd CHAR(2) NOT NULL,
[YEAR] INT NOT NULL CHECK(YEAR BETWEEN 1582 AND 4000), -- Gregorian calender from 1582 (in most countries)
[MONTH] tinyint NOT NULL CHECK(MONTH BETWEEN 1 AND 12),
[DAY] tinyint CHECK(DAY BETWEEN 1 AND 31),
day_of_week tinyint,
day_name CHAR(18),
month_name CHAR(18),
CONSTRAINT pk_dates_id PRIMARY KEY clustered(id),
CONSTRAINT uc_unique_date UNIQUE([DATE]),
CONSTRAINT chk_valid_date CHECK(isdate((yyyy+mm+dd)) = 1) )
GO

-- Declare variables
DECLARE @startdate datetime, @enddate datetime, @id INT, @YEAR INT, @MONTH INT, @DAY INT

-- Initialize variables
SET @startdate = '19980101'
SET @enddate = '20121231'
SELECT @id = 1,@YEAR = YEAR(@startdate),@MONTH = MONTH(@startdate),@DAY = DAY(@startdate)

-- Populate dates table
while 1 = 1
BEGIN
-- insert the date table
INSERT dates(id,yyyy,mm,dd,[YEAR],[MONTH],[DAY])
VALUES( @id,
SUBSTRING('0000',1,4-len(@YEAR))+ltrim(@YEAR),
SUBSTRING('00',1,2-len(@MONTH))+ltrim(@MONTH),
SUBSTRING('00',1,2-len(@DAY))+ltrim(@DAY),
@YEAR,
@MONTH,
@DAY )

-- check for end condition and return
IF (SELECT [DATE] FROM dates WHERE id = @id) = @enddate RETURN
-- recompute the variables
IF @DAY < 28 SET @DAY = @DAY + 1
ELSE IF @MONTH IN(1,3,5,7,8,10) AND @DAY = 31 SELECT @DAY = 1, @MONTH = @MONTH + 1
ELSE IF @MONTH = 12 AND @DAY = 31
BEGIN
SELECT @DAY = 1, @MONTH = 1, @YEAR = @YEAR + 1
print @YEAR
END
ELSE IF @MONTH IN(4,6,9,11) AND @DAY = 30
SELECT @DAY = 1, @MONTH = @MONTH + 1
-- A leap year is any year divisible by four except years both divisible by 100 and not divisible by 400
ELSE IF @MONTH = 2 AND @DAY = 28 AND NOT (@year%4 = 0 AND NOT( @year%4 = 0 AND @year%100 = 0 AND @year%400 != 0 ))
SELECT @DAY = 1, @MONTH = @MONTH + 1
--else
ELSE IF @MONTH = 2 AND @DAY = 29 AND (@year%4 = 0 AND NOT( @year%4 = 0 AND @year%100 = 0 AND @year%400 != 0 ))
SELECT @DAY = 1, @MONTH = @MONTH + 1
ELSE SET @DAY = @DAY + 1
SET @id = @id + 1
END
GO

-- Update the fields with name of month and weekday
UPDATE dates SET
month_name = datename(mm,CONVERT(datetime,DATE)),
day_of_week = weekdays.wd,
day_name = weekdays.dayname
FROM
( SELECT id%7 AS MOD,datepart(dw,CONVERT(datetime,DATE)) AS wd,datename(dw,CONVERT(datetime,DATE)) AS dayname
FROM dates WHERE DATE BETWEEN '20040105' AND '20040111' ) AS weekdays
WHERE
weekdays.MOD = dates.id%7
GO

SELECT * FROM dates