Generate date range in between 2 dates

Question

for the last couple of hours I have been breaking my head over this.

I want to create a result set which contains a series of dates like this:

2011-07-05
2011-07-04
2011-07-03
2011-07-02
2011-07-01
2011-06-30
2011-06-29
2011-06-28
...

Ideally between 2 dates given. But If I can say the last 30 days or the last 100 days from now that would be fine also.

Normally I would this with a CTE like this

;WITH Dates AS
(
    SELECT CONVERT(DATE, GETDATE()) [Date]
    UNION ALL
    SELECT DATEADD(DAY,-1, [Date])
    FROM Dates
    WHERE [Date] > DATEADD(DAY, -30, CONVERT(DATE, GETDATE()))
)
SELECT [Date]

But I am not allowed to use any statements that can't be executed in a subquery. The program I am using executes queries like this:

Select *
From (
    TheQuery
) as t1

This means I can't use declares, no stored procedures, no CTEs..

Is there any way I can obtain the dataset I need with these limitations?

I am using azure SQL

Check if this solves your requirement [Date Range](https://stackoverflow.com/questions/7824831/generate-dates-between-date-ranges) — DataWrangler, Jul 06 '17 at 07:33
@rg337, you can use existing system view to generate sequence. Hope you have at least one system view with resultset, having more than 30 rows `select top(30) CAST( dateadd(DAY, -ROW_NUMBER() over (order by name), GETDATE()) as DATE) from sys.all_objects` — vitalygolub, Jul 06 '17 at 07:53
If it's possible for you, it's definitely good idea to create physical Dates table. It often comes useful to have it, not just for this problem. — Nenad Zivkovic, Jul 06 '17 at 07:54
@vitalygolub This is also a nice solution, why don't you post it as answer? — Nenad Zivkovic, Jul 06 '17 at 07:55

score 2 · Answer 1 · answered Jul 06 '17 at 07:54

If you dont't want create a calendar table or a number table, nor use existing table to generate numbers/ date (see for example https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1) you could use something like this:

SELECT DATEADD(DAY, -B.N1+1, CONVERT(DATE, GETDATE())) AS D1
FROM 
(SELECT 1 AS N1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7  UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) A
CROSS JOIN (SELECT 1 AS N1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7  UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) B

score 2 · Accepted Answer · answered Jul 06 '17 at 08:04

2

You can use a recursive cte if you put it in a table valued function

CREATE FUNCTION FnDateRange
(   
    @startDate date,
    @endDate date
)
RETURNS @DateRange Table
(myDate date)
AS
begin
    with Dates_rte as
    (
        select @startDate myDate
        union all
        select cast(dateadd(day,1,myDate) as date)
        from Dates_rte
        where cast(dateadd(day,1,myDate) as date) <= @endDate
    )
    insert into @DateRange
    select * from Dates_rte option (maxrecursion 0)

    return
end
GO

select * from fnDateRange('2017-07-01','2017-07-06')

answered Jul 06 '17 at 08:04

Peter

850
5
16

easiest answer by far. Let me buy you a beer if I ever meet you. – Jul 06 '17 at 08:10
might be easiest but not best. The best solution is to either use a calendar table or a numbers table. – Zohar Peled Jul 06 '17 at 08:20
@ZoharPeled Why would a table be better? This function supports all possible dates. Would you put all possible dates in a table? The table will be huge. – Peter Jul 06 '17 at 08:36
An implementation of a calendar using a numbers table will out-perform a recursive cte. performance difference will increase as the number of records will go higher. I currently don't have sql server to run performance tests, but you can check it out yourself. Plus, it's highly unlikely you will need "all possible dates". 90% of the cases, you will need less than 20 years to each direction from current date. – Zohar Peled Jul 06 '17 at 08:46
@ZoharPeled I don't care about performance that much. This query can obtain dates from 5 years in less then a second. On the most basic azure sql plan. – Jul 06 '17 at 09:36

score 1 · Answer 3 · answered Jul 06 '17 at 10:47

DECLARE @fromdate DATE
DECLARE @todate DATE
DECLARE @tcaldate Table (CalenderDate Date);

set @fromdate='2017-04-17'
set @todate='2017-05-13'

INSERT INTO @tcaldate SELECT  TOP (DATEDIFF(DAY, @fromdate, @todate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @fromdate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;  

Select * from @tcaldate

Hope this helps...

score 0 · Answer 4 · answered Jul 06 '17 at 07:33

Well, I think the easiest way is to create calendar table and in subquery just select dates between dates. You can do this by this query:

CREATE TABLE dbo.Calendar ([Date] date)
DECLARE @startDate date, @endDate date

SET @startDate = '2000-01-01'
SET @endDate = '2020-12-31'

WHILE @startDate <= @endDate
BEGIN
INSERT INTO dbo.Calendar
SELECT @startDate

SET @startDate = DATEADD(DD,1,@startDate)
END

Selecting dates:

Select *
From dbo.Calendar WHERE [Date] BETWEEN @date1 AND @date2

Generate date range in between 2 dates

4 Answers4