Getting the dates between a range

Question

Getting the dates between a range

gaurav-404321

SSCommitted

Points: 1593
More actions
May 21, 2010 at 3:15 am

#139094

I have two variables
@StartDate and @EndDate
I want to write a SELECT Query which can give me rows of dates between the specified range
For example
@SstartDate='2010-01-01 01:00:00.000'
@EstartDate='2010-01-31 01:00:00.000'
I want to have a select query which can give me values like:
'2010-01-01 01:00:00.000'
'2010-01-01 02:00:00.000'
'2010-01-01 03:00:00.000'
'2010-01-01 04:00:00.000'
'2010-01-01 05:00:00.000'
....
....
...
...
'2010-01-31 01:00:00.000'

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

What have you tried so far?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 2

you need to have tally table in your database before running below query ...check the last link in chris morris signature for Tally table..

DECLARE @startDate datetime

DECLARE @endDate datetime

SELECT @startDate = '20090328', @endDate = '20090413'

-- dispays all dates between the date ranges ..

SELECT DATEADD(day, T.N, @StartDate) AS [Date]

FROM dbo.Tally T

WHERE (T.N >= 0 AND T.N <= DATEDIFF(day, @startDate, @endDate)

)

ORDER BY T.N

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Dohsan SSCrazy Points: 2972 More actions · Answer 3

Try this:

DECLARE

@StartDateDATETIME,

@EndDateDATETIME;

--Use DATEADD/DATEDIFF to get the start of the month/day/hour etc

SELECT

@StartDate= DATEADD(HH,DATEDIFF(HH,0,'2010-01-01 01:50:00.000'),0),

@EndDate= DATEADD(HH,DATEDIFF(HH,0,'2010-01-31 01:30:00.000'),0);

;WITH DateRange (DateRange) AS

(

SELECTDATEADD(HH, (T.N-1), @StartDate)

FROMTally AS T

WHERET.N - 1 <= DATEDIFF(HH, @StartDate, @EndDate)

)

SELECT DateRange FROM DateRange