获取起始日期和结束日期之间的日期。

Question

您好，我有一个问题。

我需要使用SQL查询根据开始日期和结束日期找到月份中的日期。 例如：StartDate: 2013-08-01 00:00:00.000, EndDate: 2015-08-01 00:00:00.000 结果应该如下：

2013-08-01 00:00:00.000,
2013-09-01 00:00:00.000,
2013-10-01 00:00:00.000,
2013-11-01 00:00:00.000,
2013-12-01 00:00:00.000,
2014-01-01 00:00:00.000,
2014-02-01 00:00:00.000,
2014-03-01 00:00:00.000,
2014-04-01 00:00:00.000,
2014-05-01 00:00:00.000,
2014-06-01 00:00:00.000,
2014-07-01 00:00:00.000,
2014-08-01 00:00:00.000,
2014-09-01 00:00:00.000,
2014-10-01 00:00:00.000,
2014-11-01 00:00:00.000,
2014-12-01 00:00:00.000,
2015-01-01 00:00:00.000,
2015-02-01 00:00:00.000,
2015-03-01 00:00:00.000,
2015-04-01 00:00:00.000,
2015-05-01 00:00:00.000,
2015-06-01 00:00:00.000,
2015-07-01 00:00:00.000,
2015-08-01 00:00:00.000

您能帮忙吗？提前致谢。

- John Doe

---

上述日期与一条记录相关。表中的每行都有自己的开始和结束日期。 - John Doe

@scsimon：我认为OP在这里提出的问题是：是否有DATE值的rowsource...是否有一种方法可以生成指定范围内的日期值集合，就像我们可以从视图中选择的那样。 - spencer7593

@spencer7593 抱歉，我忘记在删除重复投票后删除评论了。 - S3S

我会在这里使用一个计数表格 http://www.sqlservercentral.com/articles/T-SQL/62867/ 至于具体的实现，我感到困惑，因为不太清楚你想做什么。 - Sean Lange

Answer 1

计数表格会非常快...

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
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )

select '20130801'
union all
select dateadd(month,N,'20130801') 
from cteTally
where n <= datediff(month,'20130801','20150801')

更清晰地使用参数

declare @startDate date = '20130801'
declare @endDate date = '20150801'

;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
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )

select @startDate
union all
select dateadd(month,N,@startDate) 
from cteTally
where n <= datediff(month,@startDate,@endDate)

- S3S

Answer 2

如果是我，我会创建一个日历表。虽然你可以用许多复杂的SQL语句来使用datediff等函数，但是拥有一个包含100年日期的表格并将其连接起来似乎更加方便。如果您的数据中涉及到了大量的日期处理，这将是一个不错的选择。虽然这种方法可能有点“廉价”，但对我来说效果还不错。

参考链接：如何在Sql中创建一个包含100年的日历表。

然后，您只需将其与需要的数据连接，并获取该范围内的日期即可。 更新 正如@scsimon所指出的那样，链接中的被接受的答案可能并不理想。但是请阅读该链接，其中提供了一些很好的想法来创建这个表格。

- sniperd

---

我不会使用这里的被接受答案...它会很慢。提前告诉你。 - S3S

Answer 3

有很多方法可以解决这个问题。我经常使用的是递归CTE：

DECLARE @StartDate      date = '2013-08-01'
DECLARE @EndDate        date = '2015-08-01'

;WITH
    cte AS
    (
        SELECT      @StartDate      AS CalendarDate
        UNION ALL
        SELECT      DATEADD(MONTH, 1, CalendarDate)
        FROM        cte
        WHERE       CalendarDate < @EndDate
    )

SELECT      CalendarDate
FROM        cte
OPTION      (MAXRECURSION 0)

对于一小段数值范围（12个月x100年很小），这相当快。当您需要获取数百万行时，速度变慢。取决于您手头的问题，您可能需要不同的解决方案。

- Code Different

---

只是提供信息而已。像这样使用递归CTE可能会导致性能问题非常糟糕。http://www.sqlservercentral.com/articles/T-SQL/74118/ 这里的示例数据集很小，所以不会有太大影响。但我更喜欢使用计数表，速度超级快。 - Sean Lange

我非常清楚递归CTE的性能问题。根据问题的不同，这可能很重要，也可能不重要。计数表是理想的选择，但有时您没有权限在数据库中设置它。 - Code Different

这对于制定该规则的DBA来说是可耻的。但幸运的是，您可以像scsimon上面所做的那样使用ctes创建内联规则。这种类型的计数表非常快，因为它生成零读取以创建数千行。 - Sean Lange

完全披露， @SeanLange 教我如何使用计数表 - S3S

非常感谢，这真的帮了我很多，非常感激。 - John Doe

完全披露 - Jeff Moden 向我介绍了计数表的概念。我只是将火炬传递下去。我相信 Itzik Ben-Gan 通常被认为是 CTE 计数表的创意人，这种方法非常快速。在此之前，大多数人使用持久表。 - Sean Lange