How can i select all dates between date range?

Question

Post reply

How can i select all dates between date range?

rahulpatel112

SSC-Addicted

Points: 419
More actions
June 15, 2012 at 6:57 am

#258082

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

here's a couple fo examples:

note the end date, you might want to change that to be just lss than, unless you want to include midnight on 01-15-2012

--Hardcoded:

SELECT * From [SQLTable] WHERE ProductionDate >='2012-01-01' AND ProductionDate <='2012-01-15'

--Parameterized:

DECLARE @StartDate datetime,

@EndDate datetime

SET @StartDate = '2012-01-01'

SET @EndDate = '2012-01-15'

SELECT * From [SQLTable] WHERE ProductionDate >=@StartDate AND ProductionDate <=@EndDate

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 2

One possible solution:

USE tempdb

GO

CREATE TABLE MyTable (ProductionDate DATETIME NOT NULL, Quantity INT NOT NULL)

INSERT INTO MyTable

SELECT '1-1-2012',5 UNION ALL

SELECT '1-3-2012',7

-- Get the data

;WITH iCalendar AS (

select top 15 DATEADD(DD, N - 1,'1/1/2012') AS xDate

FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N

FROM master.sys.all_columns) T)

SELECT xDate, ISNULL(Quantity,0)

FROM MyTable A

RIGHT OUTER JOIN iCalendar C

ON A.ProductionDate = C.xDate

Converting oxygen into carbon dioxide, since 1955.