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.

Answer 2

Another way to do it is with a memory table. It won't choke due to recursion limitations like some of the above solutions.

DECLARE @dates AS TABLE ([Date] date);

DECLARE @date date = {d '2010-10-01'};
DECLARE @endDate date = {d '2010-11-01'};

while (@date < @endDate)
BEGIN
    INSERT INTO @dates VALUES (@date);
    SET @date = dateadd(DAY, 1, @date)
END
SELECT * FROM @dates;

SQL Fiddle

Answer 3

One way would be with a CTE:

with cte_dates as (
  select cast('20110119' as datetime) as [date]
  union all
  select dateadd(dd, 1, [date])
      from cte_dates
      where dateadd(dd, 1, [date]) <= '20111231'
)
select [date], YourColumn
    from cte_dates
        left join YourTable
            on ...
option (maxrecursion 0);