Generating an Date table in SQL

In reporting queries it happens often that you need a ruler of some sort for instance a table containing all days between a start and end date. You can generate such tables by using a looping construct but this isn't the strong point of SQL.

Using a single table containing all numbers between 0 and 59 this query can generate a time table with each year,month,day,hour and minute between 1980 and 2039 (this range can be moved or changed).

To generate the ruler table use the first statement. This does require a loop but as this only needs to be generated once this is the easiest way to go.

CREATE TABLE [Ruler] ( [value] int )

DECLARE @i int
SET @counter = 0

WHILE @counter < 60 
BEGIN
	INSERT INTO [Ruler] VALUES ( @counter )
	SET @counter = @counter + 1
END

The time table is then generated by the following statement:

SELECT 	1980 + y.value, 
	m.value, 
	d.value , 
	h.value, 
	mm.value
FROM	[time] y, [time] m, [time] d, [time] h, [time] mm
WHERE	m.value BETWEEN 1 and 12
AND	d.value BETWEEN 1 and 
		CASE 
			WHEN m.value IN (1,3,5,7,8,10,12) THEN 31 
			WHEN m.value = 2 THEN 
				CASE 
					WHEN y.value % 400 = 0 THEN 29 
					WHEN y.value % 100 = 0 then 28 
					WHEN y.value % 4 = 0 THEN 29 
					ELSE 28 
				END 
			ELSE 30 
		END
AND	h.value BETWEEN 0 and 23
AND	m.value BETWEEN 0 and 59
ORDER BY y.value, m.value, d.value, h.value, mm.value