使用一个函数获取两个日期之间的日期列表

Question

我的问题类似于这个MySQL问题，但是我想问的是针对SQL Server：

是否有一个函数或查询可以返回两个日期之间的日期列表？例如，假设有一个名为ExplodeDates的函数：

SELECT ExplodeDates('2010-01-01', '2010-01-13');

这将返回一个只有一列的表，其中包含以下值：

2010-01-01
2010-01-02
2010-01-03
2010-01-04
2010-01-05
2010-01-06
2010-01-07
2010-01-08
2010-01-09
2010-01-10
2010-01-11
2010-01-12
2010-01-13

我认为一个日历/数字表可能能够帮助我。

---

更新

我决定查看提供的三个代码答案以及执行结果 - 作为总批次的%：

• Rob Farley的答案：18%
• StingyJack的答案：41%
• KM的答案：41%

越低越好

虽然数字表解决方案（由KM和StingyJack在他们的答案中使用）是我的最爱，但我已经接受了Rob Farley的答案，因为它是最快的，比数字表方案快了三分之二。

更新2

Alivia的答案更为简洁。我已经改变了已接受的答案。

- Dan Atkinson

---

执行时间如何？% 总批次用于识别瓶颈，而不是吞吐量。您是对实际函数调用进行基准测试还是与其一起测试所有其他内容？比较小批次和大批次的结果？ （翻译后）：关于执行时间呢？总批次的百分比用于确定瓶颈，而不是吞吐量。您是否正在对实际函数调用进行基准测试，还是同时测试其它内容？比较小批次和大批次的结果？ - StingyJack

使用 SET STATISTICS TIME ON 命令，对所有三个函数传入 ('1/1/1998','12/31/2020') 参数进行测试，结果显示 CPU 时间为 0 毫秒，经过时间为 1 毫秒。但是当传入 ('1/1/1900','1921-11-27') 参数时，StingyJacks 函数无法处理该日期范围，Rob 的函数的 CPU 时间为 93 毫秒，经过时间为 93 毫秒，而我的函数的 CPU 时间为 0 毫秒，经过时间为 1 毫秒，看起来效果更好。@Dan Atkinson，你使用的是哪种测试方法？如果你包含了一次性数字表设置，那么这是非常有缺陷的方式，因为它不能反映实际使用的性能。 - KM.

@KM 和 @StingyJack。感谢你们教我正确的基准测试方法。KM，感谢你费心指出实际的基准测试结果。我会在我的数据库上运行一些测试，并相应地更新问题。再次感谢！ - Dan Atkinson

你为什么改变了答案？Alivia的答案需要一个提示来确保它包含足够的值，而且它不是所要求的函数。 - Rob Farley

OP更改了最正确的答案，选择了Alivia的答案，因为它更加“简洁”。这是一个非常糟糕的决定，因为它也需要更多的CPU资源，速度与普通WHILE循环一样慢，比事务WHILE循环还要慢，并且逻辑I/O使用量比普通WHILE循环高8倍。我强烈建议避免使用这种方法（增量递归CTE或rCTE）。Rob Farely的答案是一个iTVF（内联表值函数），使得使用比rCTE方法更加容易。 - Jeff Moden

Answer 1

这几行代码是 SQL Server 中简单的解答。

WITH mycte AS
(
  SELECT CAST('2011-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    mycte   
  WHERE   DateValue + 1 < '2021-12-31'
)

SELECT  DateValue
FROM    mycte
OPTION (MAXRECURSION 0)

- Alivia

---

我想知道为什么 '2011-01-01' 对我不起作用，但 '20110101' 可以。 - greg121

这太棒了！读者们：根据您的需求设置where子句以包含或排除最终日期。点赞。 - Yogi

OP更改了最正确的答案，选择了Alivia的答案，因为它更加“简洁”。这是一个非常糟糕的决定，因为它也需要更多的CPU资源，速度与普通WHILE循环一样慢，比事务WHILE循环还要慢，并且逻辑I/O使用量比普通WHILE循环高8倍。我强烈建议避免使用这种方法（增量递归CTE或rCTE）。Rob Farely的答案是一个iTVF（内联表值函数），使得使用比rCTE方法更加容易。 - Jeff Moden

你也不能在视图或 TVF 中使用它，因为你无法在视图/函数中使用 OPTION (MAXRECURSION 0)。我更好奇其他答案是如何不需要 OPTION (MAXRECURSION 0)，因为那是我使用情况的唯一解决方案。我绝对不能使用 OPTION (MAXRECURSION 0)。 - Code Novice

Answer 2

试试这样做：

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
with 
 N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

然后您使用：

SELECT *
FROM dbo.ExplodeDates('20090401','20090531') as d;

编辑后（接受答案之后）：

请注意... 如果您已经有足够大的nums表，则应使用：

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

你可以使用以下代码创建这样的表格：

CREATE TABLE dbo.nums (num int PRIMARY KEY);
INSERT dbo.nums values (1);
GO
INSERT dbo.nums SELECT num + (SELECT COUNT(*) FROM nums) FROM nums
GO 20

这些代码将创建一个包含100万行数字的表格......比逐个插入要快得多。

你不应该使用涉及 BEGIN 和 END 的函数来创建 ExplodeDates 函数，因为查询优化器将无法简化查询。

- Rob Farley

---

感谢您抽出时间改进您的答案。我也不知道使用BEGIN和END会阻止查询优化器发挥作用。谢谢！ - Dan Atkinson

如果我可以多次点赞的话，我会这么做的——它的性能非常卓越。 我测试了一个简单版本，其中nums是一个数字表，有一个聚集索引。当日期差为2天时，CTE的效果超过聚集索引的两倍（28% vs 72%），但如果日期差为37年，则CTE版本只有3%，而表格的执行效率为97%! 我希望我知道为什么它如此快... - Jonathan Sayce

这是因为它不需要进行任何输入/输出操作。 - Rob Farley

这应该是被接受的答案，因为它消除了使用OPTION (MAXRECURSION 0)的限制。如果您需要创建一个TVF或View，这是唯一的答案。 - Code Novice

我已经被说服这应该是正确的答案。 - Dan Atkinson

Answer 3

这段代码完全符合您的要求，是从Will先前的帖子修改而来。无需使用帮助表或循环。

WITH date_range (calc_date) AS (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, '2010-01-13') - DATEDIFF(DAY, '2010-01-01', '2010-01-13'), 0)
        UNION ALL SELECT DATEADD(DAY, 1, calc_date)
            FROM date_range
            WHERE DATEADD(DAY, 1, calc_date) <= '2010-01-13')
SELECT calc_date
FROM date_range;

- Jason

---

在更复杂的日期集上，我遇到了以下错误：The statement terminated. The maximum recursion 100 has been exhausted before statement completion. 所以，我要指出，对于希望在大范围内使用此答案的其他人，您需要添加一个maxrecursion值 - OPTION (MAXRECURSION 0)。 - Dan Atkinson

在某些使用情况下，无法使用相同的方法。无法在视图或 TVF 中使用此方法，可能还有其他一些情况也不可行。 - Code Novice

Answer 4

DECLARE @MinDate DATETIME = '2012-09-23 00:02:00.000',
    @MaxDate DATETIME = '2012-09-25 00:00:00.000';

SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1) Dates = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

- Surinder Singh

Answer 5

我是一名Oracle工程师，但我相信微软SQL Server也支持Connect By子句。

select  sysdate + level
from    dual
connect by level <= 10 ;

输出结果为：

SYSDATE+LEVEL
05-SEP-09
06-SEP-09
07-SEP-09
08-SEP-09
09-SEP-09
10-SEP-09
11-SEP-09
12-SEP-09
13-SEP-09
14-SEP-09

Dual是Oracle自带的一个“虚拟”表（它只包含1行数据，单列的值为“dummy”）。

- Brian

---

SQL Server没有内置的表“dual”，您需要像我在示例代码中所做的那样创建自己的表。我认为SQL Server中的“sysdate”是GETDATE()，而“connect by”不是有效的语法。 - KM.

因此，在SQL Server中，您也可以从无处选择。在SQL Server中，SELECT GETDATE()是一行有效的代码，但在Oracle中不是，即使您将GETDATE()函数替换为其SYSDATE同类。 - Will Marcouiller

你说得对，Brian，在Oracle中我们会这样做。在Oracle和PL/SQL中有很多有趣的特性，这些特性在TSQL和SQL Server中并不存在。这是Sybase的错！;-) SQL Server主要基于Sybase TSQL语言。 - Will Marcouiller

SELECT GETDATE() 不会产生一个集合，只会得到一行数据。在Oracle中使用_dual_，你可以得到一个集合。 - KM.

有点离题：实际上，列名是“DUMMY”，值为“X”。;-) - WojtusJ

Answer 6

这些日期是否已经在数据库中，还是你只想知道两个日期之间的天数？如果是前者，你可以使用BETWEEN或<= >=来查找日期之间的数据。

例如：

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

或者

SELECT column_name(s)
FROM table_name
WHERE column_name
value1 >= column_name
AND column_name =< value2

- Phill Pafford

Answer 7

你所需要做的就是更改下面提供的代码中的硬编码值

DECLARE @firstDate datetime
    DECLARE @secondDate datetime
    DECLARE @totalDays  INT
    SELECT @firstDate = getDate() - 30
    SELECT @secondDate = getDate()

    DECLARE @index INT
    SELECT @index = 0
    SELECT @totalDays = datediff(day, @firstDate, @secondDate)

    CREATE TABLE #temp
    (
         ID INT NOT NULL IDENTITY(1,1)
        ,CommonDate DATETIME NULL
    )

    WHILE @index < @totalDays
        BEGIN

            INSERT INTO #temp (CommonDate) VALUES  (DATEADD(Day, @index, @firstDate))   
            SELECT @index = @index + 1
        END

    SELECT CONVERT(VARCHAR(10), CommonDate, 102) as [Date Between] FROM #temp

    DROP TABLE #temp

- Shiva

Answer 8

一些想法：

如果您需要按顺序循环遍历日期列表，可以设置开始日期和天数参数，并在创建日期并使用它时执行 while 循环？

使用 C# CLR 存储过程并用 C# 编写代码。

在代码中在数据库外执行此操作。

- Mark Redman

---

如果性能很关键，CLR存储过程绝对是可行的方式。 - StingyJack

@StingyJack，不行。数字表格会更有效率，可以看一下我的答案示例。 - KM.

性能并不是关键，因为这个函数最坏情况下每小时只会被调用一次，平均情况下每天只会被调用一次，并且还会被缓存。但我不想使用CLR来完成这个任务。 - Dan Atkinson

@KM - 冒着引发争论的风险，你应该知道 SQL 不是为处理过程操作而设计的，而且在处理这些操作时性能表现不佳。如果你需要执行类似的操作，最好由应用程序代码来处理。 - StingyJack

@StingyJack，我的函数是过程化的吗？除了验证检查之外，它只是一个简单的查询，为可变行数的固定日期添加偏移量。它与在项目详细信息中进行的计算没有太大区别，例如根据数量和单价计算总价，带或不带货币单位。 - KM.

Answer 9

这个查询适用于 Microsoft SQL Server。

select distinct format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' ) as aDate
       from (
             SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n as v
             FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
                    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
                    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
                  (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
       ) a
       where format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' ) < cast('2010-01-13' as datetime)
       order by aDate asc;

现在让我们来看看它是如何工作的。

内部查询只返回一个从0到9999的整数列表。它将为我们提供一个计算日期的10,000个值的范围。您可以通过为十万和百万等添加行来获取更多日期。

SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n as v
         FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
                (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
                (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
              (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
   ) a;

这部分将字符串转换为日期，并从内部查询中添加一个数字。

cast('2010-01-01' as datetime) + ( a.v / 10 )

然后我们将结果转换为您想要的格式。这也是列名！

format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' )

接下来，我们仅提取不同的值，并将列名别名设置为aDate。

distinct format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' ) as aDate

我们使用where子句来过滤出你想要的日期范围内的数据。请注意，由于SQL Server不接受列别名aDate，因此我们在where子句中使用列名。

where format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' ) < cast('2010-01-13' as datetime)

最后，我们对结果进行排序。

   order by aDate asc;

- RichS

Answer 10

有点晚了，但我相当喜欢这个解决方案。

CREATE FUNCTION ExplodeDates(@startDate DateTime, @endDate DateTime)
RETURNS table as
return (
    SELECT  TOP (DATEDIFF(DAY, @startDate, @endDate) + 1)
            DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @startDate) AS DATE
    FROM    sys.all_objects a
            CROSS JOIN sys.all_objects b
            )

- Schuits