7

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?

kacalapy
  • 9,806
  • 20
  • 74
  • 119
  • You're looking for a sequence generator; [this](http://msdn.microsoft.com/en-us/library/aa175802(v=sql.80).aspx) was the article I used to base mine on the last time I needed one. Although, of course, if you just want to do it once, a cursor or a simple loop would do just fine... – Matt Gibson Jan 19 '11 at 16:03

3 Answers3

14

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Finally! Thank you for this. This seemed like it should be so simple and yet all the other answers to very similar questions involved creating functions or permanent tables. This is exactly the solution I needed and works perfectly. – Ashton Sheets Jun 19 '13 at 21:35
  • It's the easier way, but if it grows to thousands or millions of dates, it has a poor performance compared to a temporal or permanent table. Check this out: http://www.sqlservercentral.com/articles/T-SQL/74118/ – Guillermo Gutiérrez Nov 04 '13 at 14:59
7

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

komma8.komma1
  • 1,250
  • 2
  • 14
  • 20
6

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);
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • 1
    +1 because your post was submitted at the exact minute of the accepted answer, and because your WHERE clause is more robust than his (it works for intervals greater than 1, which is overlooked in accepted answer). – kmote Aug 06 '15 at 15:41