How can i select all dates between date range?

Question

I have one SQL Table with 2 columns as below

Column1: ProductionDate - DateTime - Not NULL

Column2: Quantity - Int - Not NULL

Now There are 2 Records in Table

1-1-2012, 5

1-3-2012, 7

Output of Result should be as below if i give date range StartDate as 1-1-2012 and EndDate as 1-15-2012

1-1-2012 5

1-2-2012 0

1-3-2012 7

1-4-2012 0

1-5-2012 0

1-6-2012 0

.

.

.

1-15-2012 0

Means Query should return all the dates of given range with Quantity and if no entry in Table then 0 for Quantity.

How to Do it? Please suggest with Query

score 0 · Answer 1 · edited May 23 '17 at 10:24

Here's one very optimistic draft on what you can use ( source - here )

declare @startDate datetime;
declare @endDate datetime;

set @startDate = '2012-02-09';
set @endDate = '2012-02-15';

WITH span AS (
  SELECT @startDate AS dt
  UNION ALL
  SELECT DATEADD(dd, 1, dt)
    FROM span s
   WHERE DATEADD(dd, 1, dt) <= @endDate)

select s.dt, t.Quantity from span s
join table t
on s.dt = t.ProductionDate

How can i select all dates between date range?

1 Answers1