CODEWITHSUNDEEP
sqlsql-server
user3691566
user3691566

Reputation: 303

Get all dates in date range

Im creating a query that gets data based on 1 day currently. I would like it to get data for all dates in a date range so just after ideas and basic structure for how best to go about this.

example query

Select date
from table
where date between 01/01/2014 and 05/01/2014

Ideally id like the results returns as follows

01/01/2014

02/01/2014

03/01/2014

04/01/2014

05/01/2014

Upvotes: 0

Views: 206

Answers (3)

Sean Lange
Sean Lange

Reputation: 33581

Here is how you could do this with a tally table instead of a triangular join.

create Procedure GenerateDateRange(@FromDate Date, @ToDate Date)
As
    WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    E6(N) AS (SELECT 1 FROM E4 a, E2 b),
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E6
    )

    select DATEADD(day, t.N - 1, @FromDate) as MyDate
    from cteTally t
    where N <= DATEDIFF(day, @FromDate, @ToDate) + 1

go

Now let's compare the performance of this versus the triangular join method that was chosen as the answer.

declare @FromDate date = '1000-01-01', @ToDate date = '3000-01-01'

exec spGenerateDateRange @FromDate, @ToDate
exec GenerateDateRange @FromDate, @ToDate

Upvotes: 2

Siyual
Siyual

Reputation: 16917

You mentioned that you wanted a stored procedure for this, so this should work for you:

Create Procedure spGenerateDateRange(@FromDate Date, @ToDate Date)
As Begin

    ;With Date (Date) As
    (
        Select  @FromDate Union All
        Select  DateAdd(Day, 1, Date)
        From    Date
        Where   Date < @ToDate
    )
    Select  Date
    From    Date
    Option  (MaxRecursion 0)

End

Upvotes: 1

mohan111
mohan111

Reputation: 8865

May be this works for you 

DECLARE @DateFrom date, @DateTo date;
    SET @DateFrom='20140801';
    SET @DateTo='20140805';

    WITH DateRange(date)
    AS
    ( 
    SELECT @DateFrom 
    UNION ALL
    SELECT DateAdd(day,1,D.date) FROM DateRange D WHERE D.date < @DateTo
    )
    SELECT date FROM DateRange

Upvotes: 1

Related Questions