生成2个日期之间的日期范围

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.

理想情况下，在两个日期之间。但是，如果我可以说从现在开始的过去30天或过去100天也没关系。

Normally I would this with a CTE like this

通常我会用这样的CTE

;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..

这意味着我不能使用声明，没有存储过程，没有CTE ..

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

有什么办法可以通过这些限制获得我需要的数据集吗？

I am using azure SQL

我正在使用azure SQL

4 个解决方案

#1

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

如果将其放在表值函数中，则可以使用递归cte

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')

#2

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:

如果您不想创建日历表或数字表，也不要使用现有表来生成数字/日期（请参阅https://sqlperformance.com/2013/01/t-sql-queries/generate-a- set-1）你可以使用这样的东西：

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

#3

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...

希望这可以帮助...

#4

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

#1