How can i select all dates between date range?

[RA UL PATEL]

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

[bobrien100]

You could accomplish this with a script rather than a single query. I have not run this and if you are allergic to table vars then stop reading. Presuming you have a begin date that is less than or equal to the end date you can proceed like this...

declare @loopDate as datetime
declare @dateRange as table ( aDate datetime )

set @loopDate = @beginDate
insert into @dateRange (aDate) values (@loopDate ) -- beginning of your date range

while @loopDate < @endDate
begin
Dateadd(day, @loopDate, 1)
insert into @dateRange (aDate) values (@loopDate )
end

select aDate, isnull(quantity, 0)
from
yourTable a
left outer join @dateRange b on
b.aDate = a.Productiondate

I have not tested this. There might be a syntax flaw. Flame away. - Bill O'Brien

[Corporal Agarn]

Usually someone will say use a generated (permanent) table, however your solution seems logical. P.S. I have not tested it either. I hope it will work for rahulpatel112.

[Mycroft Holmes]

It is amazing what you can find in these forums if you look. Bob has got it right and if you had even looked just a little further you would have found this answer[^] 4 threads down!

Never underestimate the power of human stupidity RAH

[Jorgen Andersson]

If you need it to be a query and not a Stored Procedure you can try this:

WITH DateRange(Date) AS
(
SELECT @StartDate Date
UNION ALL
SELECT DATEADD(day, 1, Date) Date
FROM DateRange
WHERE Date < @EndDate
)
SELECT dr.date
,case mt.quantity when null then 0 else mt.quantity end quantity
FROM DateRange dr
LEFT OUTER JOIN MyTable mt
ON dr.date = mt.productiondate

I haven't tested it, but you'll get the idea.

Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions

[vvashishta]

DECLARE @TargetDate SMALLDATETIME DECLARE @InitialDate SMALLDATETIME SET @InitialDate = '06-19-2012' SET @TargetDate = '06-25-2012' WHILE @InitialDate <= @TargetDate BEGIN PRINT @InitialDate SELECT @InitialDate = DATEADD( D, 1, @InitialDate ) END - Happy Coding - Vishal Vashishta