使用函数获取两个日期之间的日期列表 [英] Get a list of dates between two dates using a function

问题描述

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

My question is similar to this MySQL question, but intended for SQL Server:

是否有函数或查询将返回两个日期之间的日期列表？例如，让我们说有一个名为ExplodeDates的函数：

Is there a function or a query that will return a list of days between two dates? For example, lets say there is a function called ExplodeDates:

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

这将返回一个列表，其值为：

This would return a single column table with the values:

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

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

I'm thinking that a calendar/numbers table might be able to help me here.

更新

我决定看看提供的三个代码答案，执行结果 - 作为总批次的百分比是：

I decided to have a look at the three code answers provided, and the results of the execution - as a % of the total batch - are:

• Rob Farley的回答：18％


• Sti ngyjack的答案：41％


• KM的答案：41％

• Rob Farley's answer : 18%
• StingyJack's answer : 41%
• KM's answer : 41%

下更好的

我已经接受了Rob Farley的答案，因为它是最快的，即使数字表解决方案（由KM和StingyJack在他们的答案中使用）是我最喜欢的东西。 Rob Farley的速度是三分之二。

I have accepted Rob Farley's answer, as it was the fastest, even though numbers table solutions (used by both KM and StingyJack in their answers) are something of a favourite of mine. Rob Farley's was two-thirds faster.

推荐答案

尝试这样的一个例子：

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;

编辑（接受后）：

请注意...如果您已经有一个足够大的数字表，那么您应该使用：

Please note... if you already have a sufficiently large nums table then you should use:

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

您可以使用以下方式创建此类表：

And you can create such a table using:

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

这些行将创建一个包含1M行的数字表，并且比一个接一个插入更快。

These lines will create a table of numbers containing 1M rows... and far quicker than inserting them one by one.

您不应该使用涉及BEGIN和END的函数创建您的ExplodeDate函数，因为Query Optimizer完全无法简化查询。

You should NOT create your ExplodeDates function using a function that involves BEGIN and END, as the Query Optimizer becomes unable to simplify the query at all.

这篇关于使用函数获取两个日期之间的日期列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！