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

3 comments
yakov 14 Oct 2011 at 9:32 am

Hi Derek,
the script does not work in sql 2008 r2.
if @day < 28 set @day = @day + 1

please advise

Anonymous 13 Mar 2011 at 7:35 am

hi this is venu your postings are nice. but in this create table there are two errors i have at the time of executing please verify and correct it.

Ramakrishnan 28 Sep 2010 at 2:18 am

in sql server 2005, Display the numner which is divisible by 30 between 1 to 30 in basic formation.

Featured Articles

 Site Author