TSQL to generate date lookup table data

Posted on November 10, 2011, 1:20 pm, by Rhys, under T-SQL.

I needed to generate a range of data about dates for a lookup table. There’s an elegant solution using a recursive cte that does the job;

?View Code TSQL
WITH daysCte
(
	d 
)
AS
(
	SELECT CONVERT(DATETIME, '1 January 2011') AS d -- starting date
	UNION ALL
	SELECT DATEADD(D, 1, d)
	FROM daysCte
	WHERE DATEPART(yyyy, d) <= 2012 -- stop year
)
SELECT d, 
	   DATEPART(wk, d) AS week_number,
	   DATENAME(dw, d) AS day_name,
	   DATENAME(m, d) AS month_name,
	   DATENAME(q, d) AS [quarter]
FROM daysCte 
OPTION (MAXRECURSION 800); -- set > number of days you want data for

This will display something looking like below;

d			week_number	day_name	month_name	quarter
2011-01-01 00:00:00.000	1		Saturday	January		1
2011-01-02 00:00:00.000	2		Sunday		January		1
2011-01-03 00:00:00.000	2		Monday		January		1
2011-01-04 00:00:00.000	2		Tuesday		January		1
2011-01-05 00:00:00.000	2		Wednesday	January		1
2011-01-06 00:00:00.000	2		Thursday	January		1
2011-01-07 00:00:00.000	2		Friday		January		1

Comment (RSS)  |  Trackback

2 Comments

  1. Stephanie Sullivan says:
    November 25, 2011 at 3:16 pm

    I recommend the script by Sean Smith for your perusal.
    http://www.sqlservercentral.com/scripts/Date/68389/
    It too makes use of CTEs.

    I took this script and converted it into mysql (bye bye awesome ctes) for my place of work as well as creating a bank holiday input table and calculating working days completed and remaining also.

    It’s a very good script and great if you need to build something permanent. I do like CTEs as a way of doing such things on the fly though for reports as it removes the hassle of getting a table created in live.

  2. Rhys says:
    November 25, 2011 at 6:03 pm

    Hi Stephanie,

    Looks great thanks.

    Cheers,

    Rhys

Leave a Reply

 
«
»