sql server 2005 - generate sql temp table of sequential dates to left outer join to

Question

i have a table of data that i want to select out via stored proc such that users can connect a MS excel front end to it and use the raw data as a source to graph.

The problem with the raw data of the table is there exist gaps in the dates because if there is no data for a given day (there is no records with that date) then when users try to graph it it creates problems.

I want too update my stored proc to left outer join to a temp table of dates so that the right side will come in as nulls that i can cast to zero's for them to have a simple plotting experience.

how do i best generate a one field table of dates between a start and end date?

Answer 1

In SQL Server 2005 and up, you can use something like this (a Common Table Expression CTE) to do this:

DECLARE @DateFrom DATETIME
SET @DateFrom = '2011-01-01'

DECLARE @DateTo DATETIME
SET @DateTo = '2011-01-10'

;WITH DateRanges AS
(
    SELECT @DateFrom AS 'DateValue'
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateRanges
    WHERE DateValue < @DateTo
)
SELECT * FROM DateRanges

You could LEFT OUTER JOIN this CTE against your table and return the result.