我的问题类似于此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


我认为日历/数字表可以在这里为我提供帮助。


更新

我决定看看提供的三个代码答案，执行结果（占总批处理的百分比）为：



罗布·法利的答案：18％

StingyJack的答案：41％

KM的答案：41％

较低更好

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

更新2

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

评论

SQL中的循环性能将达到S U CK。尝试这些答案时，请记住这一点。
执行时间呢？总批次的百分比用于确定瓶颈，而不是吞吐量。您是否要对实际的函数调用或其他所有函数进行基准测试？比较小批量和大批量的结果？
在（（1/1/1998），'12/31/2020'）调用的所有三个函数上使用SET STATISTICS TIME，将报告相同的CPU时间= 0毫秒，经过的时间= 1毫秒。当用（'1/1/1900'，'1921-11-27'）调用Rob's and mine时，StingyJacks无法达到我得到Rob's的日期范围：CPU时间= 93 ms，经过时间= 93 ms。我得到我的：我的CPU时间= 0毫秒，经过的时间= 1毫秒。我的看起来更好。您使用@Dan Atkinson的哪种测试方法？如果您包含一次“编号”表设置，那是一种非常不完善的方式，因为它不能反映实际的使用性能。
@KM和@StingyJack。谢谢你们两个对我进行基准测试的正确方法的教育。 KM，感谢您费心指出实际基准测试结果。我将在数据库上运行一些文件，并相应地更新问题。再次感谢！
你为什么改变答案？ Alivia的答案需要提示以确保它包含足够的值，并且它不是所请求的功能。

#1 楼

这几行是sql server中此问题的简单答案。


评论

我不知道为什么'2011-01-01'对我不起作用，而对'20110101'对我有用。

– greg121
2015年11月30日，12：40
这太棒了！读者：设置where子句以根据您的要求包括或排除最后日期。投票。

–罗伯托
18年5月18日在22:19

#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


这些行将创建一个包含1M行的数字表，并且比一张一张地插入它们要快得多。

请勿使用涉及BEGIN和END的函数来创建ExplodeDates函数，因为查询优化器根本无法简化查询。

评论

感谢您抽出宝贵的时间来改善答案。我也不知道使用BEGIN和END会阻止Query Optimizer正常工作。谢谢！

–丹·阿特金森
09年9月5日上午10:57
如果我可以投票一次以上，那么它的表现将是惊人的。我对一个简单的版本进行了测试，其中nums是一个数字表，数字上具有聚集索引。如果日期差为2天，则CTE会比聚集索引高2倍（28％比72％），但是如果日期差为37年，则CTE版本为表的3％对97％！我希望我知道为什么这么快...

–乔纳森·赛斯（Jonathan Sayce）
2011-09-1 12:56


这是因为它不必执行任何I / O。

–罗布·法利
2011年9月3日在12:29

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


评论

在更复杂的日期集上出现以下错误：语句终止。在语句完成之前，最大递归100已用尽。因此，对于那些希望在较大范围内使用此答案的人，我应该指出您需要添加maxrecursion值-OPTION（MAXRECURSION 0）。

–丹·阿特金森
2012年10月23日在8:41


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


#5 楼

我是一个Oracle专家，但我相信MS 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”作为单列的值）。

评论

SQL Server没有内置表“ dual”，您需要像在示例代码中一样创建自己的表。我认为“ sysdate”在SQL Server中是GETDATE（），而“ connect by”是无效的语法。

–KM。
09年9月4日在11:43
因此，您也可能在SQL Server中无处选择。 SELECT GETDATE（）是SQL Server（而不是Oracle）中的有效代码行，即使您用其SYSDATE替代了GETDATE（）函数也是如此。

–威尔·马库里尔（Will Marcouiller）
09年9月4日在11:48
您是对的Brian，在Oracle中，我们将采用这种方式。 TSQL和SQL Server中没有包含Oracle和PL / SQL中许多有趣的功能。这是Sybase的错！ ;-) SQL Server主要基于Sysbase TSQL语言。

–威尔·马库里尔（Will Marcouiller）
09年9月4日在11:49
SELECT GETDATE（）不会仅产生一行记录。在Oracle中使用Dual，您会得到一套。

–KM。
09年9月4日在11:54
小小的顶部：实际上列名称是“ DUMMY”，值是“ X”。 ;-)

– WojtusJ
2012年9月18日在8:02

#6 楼

一些想法：

如果需要列表日期来循环浏览它们，可以有一个“开始日期”和“天数”参数，并在创建日期和使用它的同时做一会儿循环？ />
使用C＃CLR存储过程并在C＃中编写代码。

在数据库外的代码中进行此操作

评论

如果性能至关重要，则CLR存储的proc是必经之路。

–StingyJack
09年9月4日在11:59
@StingyJack，没办法。 Numbers表会更有效，请参阅我的答案以获取有关此方法的示例。

–KM。
09年9月4日在12:34
性能并不是至关重要的，因为在最坏的情况下，每小时只调用一次，平均一天才调用一次，然后对其进行缓存。我不想使用CLR来做到这一点。

–丹·阿特金森
09年9月4日于13:30
@KM-在这里有引发大战的危险，您应该知道SQL并非旨在处理程序操作，并且对它们的执行效果很差。如果您需要执行此类操作，则最好由应用程序代码处理。

–StingyJack
09-09-4 13:39
@StingyJack，我的函数程序如何？除了验证检查之外，这是一个简单的查询，它为一定数量的行添加了一个固定日期的偏移量。与项目详细信息中的计算没什么不同，例如，根据有无货币的数量和单价计算总价格。

–KM。
09年9月4日14:35

#7 楼

所有这些日期都已经在数据库中了吗？还是只想知道两个日期之间的日期？如果是第一个，则可以使用BETWEEN或<=> =查找日期之间的日期

示例：

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


OR 

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


#8 楼

您所要做的只是更改下面提供的代码中的硬编码值

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


#9 楼

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

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
            )


#10 楼

在使用我的函数之前，您需要设置一个“帮助器”表，每个数据库只需要执行一次此操作：

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END


这是该函数： 

CREATE FUNCTION dbo.ListDates
(
     @StartDate    char(10)  
    ,@EndDate      char(10)
)
RETURNS
@DateList table
(
    Date datetime
)
AS
BEGIN


IF ISDATE(@StartDate)!=1 OR ISDATE(@EndDate)!=1
BEGIN
    RETURN
END

INSERT INTO @DateList
        (Date)
    SELECT
        CONVERT(datetime,@StartDate)+n.Number-1
        FROM Numbers  n
        WHERE Number<=DATEDIFF(day,@StartDate,CONVERT(datetime,@EndDate)+1)


RETURN

END --Function


使用此：

select * from dbo.ListDates('2010-01-01', '2010-01-13')


输出： />

评论

有“ WHILE”的地方有一个循环！

–StingyJack
09年9月4日在12:28
@StingyJack，您疯了吗，我的函数中没有循环。我使用循环来设置Numbers表，以便人们可以轻松查看它的作用。我可以在那里轻松地使用CTE（例如从这里：sommarskog.se/arrays-in-sql-2005.html#tblnum），但它会使某些人感到困惑。对于表的一次性设置，这不是问题。

–KM。
09年9月4日在12:30
他指的是您在Numbers表中输入值的位置。

–史密斯先生
09年9月4日在12:31
@KM ..您的代码中肯定有一个循环。请帮我一个忙，然后再打击我...测试使用您的数字表和10000个数字的方法创建数字表所花费的时间。您会看到我从Moden借来的那一部影片将击败循环播放的作品数个数量级。接下来，测试每个功能需要花费多长时间。我敢打赌，我提供的解决方案将比您获得的解决方案更好。下次要DV某人时，最好确保您有充分的理由。

–StingyJack
09-09-4 13:44
我喜欢数字表的想法！它们用途广泛，可用于其他用途。

–丹·阿特金森
09年9月4日在14:20

#11 楼



WITH date_range (calc_date) AS (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP) - 6, 0)
        UNION ALL SELECT DATEADD(DAY, 1, calc_date)
            FROM date_range
            WHERE DATEADD(DAY, 1, calc_date) < CURRENT_TIMESTAMP)
SELECT calc_date
FROM date_range;


但是临时表也是一种很好的方法。也许您还应该考虑填充的日历表。

评论

您只需要使用此代码创建一个存储过程，并用您的CURRENT_TIMESTAMP值或类似的值替换即可。

–威尔·马库里尔（Will Marcouiller）
09年9月4日在12:24

#12 楼

绝对是一个数字表，但是如果您确实需要性能，则可能要使用Mark Redman的CLR proc / assembly的想法。

如何创建日期表（以及创建数字表的超快速方法）

/*Gets a list of integers into a temp table (Jeff Moden's idea from SqlServerCentral.com)*/
 SELECT TOP 10950 /*30 years of days*/
        IDENTITY(INT,1,1) as N
   INTO #Numbers
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2


/*Create the dates table*/
CREATE TABLE [TableOfDates](
    [fld_date] [datetime] NOT NULL,
 CONSTRAINT [PK_TableOfDates] PRIMARY KEY CLUSTERED 
(
    [fld_date] ASC
)WITH FILLFACTOR = 99 ON [PRIMARY]
) ON [PRIMARY]

/*fill the table with dates*/
DECLARE @daysFromFirstDateInTheTable int
DECLARE @firstDateInTheTable DATETIME

SET @firstDateInTheTable = '01/01/1998'
SET @daysFromFirstDateInTheTable = (SELECT (DATEDIFF(dd, @firstDateInTheTable ,GETDATE()) + 1))

INSERT INTO
      TableOfDates
SELECT 
      DATEADD(dd,nums.n - @daysFromFirstDateInTheTable, CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)) as FLD_Date
FROM #Numbers nums

现在您有了一个表日期，您可以使用KM之类的函数（非PROC）来获取它们的表。 

CREATE FUNCTION dbo.ListDates
(
     @StartDate    DATETIME  
    ,@EndDate      DATETIME
)
RETURNS
@DateList table
(
    Date datetime
)
AS
BEGIN

/*add some validation logic of your own to make sure that the inputs are sound.Adjust the rest as needed*/

  INSERT INTO
    @DateList
  SELECT FLD_Date FROM TableOfDates (NOLOCK) WHERE FLD_Date >= @StartDate AND FLD_Date <= @EndDate
  RETURN
END


评论

为什么需要一个日期表，而只是使用Numbers表计算它们？

–KM。
09年9月4日在12:36
因为即时计算它们会导致性能降低，尤其是如果将它们内联使用并针对语句访问的每一行进行评估。

–StingyJack
09年9月4日在13:37
消息137，级别15，状态2，第23行必须声明标量变量“ @”。这个（SELECT（DATEDIFF（dd，@ firstDateInTheTable，GETDATE（））+ 1））应该是（SELECT（DATEDIFF（dd，@firstDateInTheTable，GETDATE（））+ 1））_

–KM。
09年9月4日在14:30

#13 楼

Declare @date1 date = '2016-01-01'
              ,@date2 date = '2016-03-31'
              ,@date_index date
Declare @calender table (D date)
SET @date_index = @date1
WHILE @date_index<=@date2
BEGIN
INSERT INTO @calender
SELECT @date_index

SET @date_index = dateadd(day,1,@date_index)

IF @date_index>@date2
Break
ELSE
Continue
END


#14 楼

-###一打中的六个。假设MsSql的另一种方法

Declare @MonthStart    datetime   = convert(DateTime,'07/01/2016')
Declare @MonthEnd      datetime   = convert(DateTime,'07/31/2016')
Declare @DayCount_int       Int   = 0 
Declare @WhileCount_int     Int   = 0

set @DayCount_int = DATEDIFF(DAY, @MonthStart, @MonthEnd)
select @WhileCount_int
WHILE @WhileCount_int < @DayCount_int + 1
BEGIN
   print convert(Varchar(24),DateAdd(day,@WhileCount_int,@MonthStart),101)
   SET @WhileCount_int = @WhileCount_int + 1;
END;


#15 楼

如果您要打印从特定年份到当前日期的年份。刚刚更改了接受的答案。

WITH mycte AS
    (
      SELECT YEAR(CONVERT(DATE, '2006-01-01',102)) DateValue
      UNION ALL
      SELECT  DateValue + 1
      FROM    mycte   
      WHERE   DateValue + 1 < = YEAR(GETDATE())
    )
    SELECT  DateValue
    FROM    mycte

OPTION (MAXRECURSION 0)


#16 楼

此查询可在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在where子句中不接受列别名aDate，因此我们在此处使用列名。

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


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

   order by aDate asc;


#17 楼

如果您处于像我这样的情况，其中禁止使用过程和函数，并且您的sql用户没有插入权限，因此不允许插入，也“不允许设置/声明类似@c之类的临时变量”，但是您想要要生成特定时期（例如本年度）的日期列表以进行汇总，请使用此

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2017-01-01' and '2017-12-31'


#18 楼

WITH TEMP (DIA, SIGUIENTE_DIA ) AS
           (SELECT 
               1, 
               CAST(@FECHAINI AS DATE)
            FROM 
               DUAL
           UNION ALL
            SELECT 
               DIA, 
               DATEADD(DAY, DIA, SIGUIENTE_DIA)
            FROM 
               TEMP
            WHERE
               DIA < DATEDIFF(DAY,  @FECHAINI, @FECHAFIN)   
               AND DATEADD(DAY, 1, SIGUIENTE_DIA) <=  CAST(@FECHAFIN AS DATE)
           )
           SELECT 
              SIGUIENTE_DIA AS CALENDARIO 
           FROM
              TEMP
           ORDER BY   
              SIGUIENTE_DIA


详细信息在表DUAL上，但如果您将此表交换为虚拟表，则可以使用。

#19 楼

SELECT  dateadd(dd,DAYS,'2013-09-07 00:00:00') DATES
INTO        #TEMP1
FROM
(SELECT TOP 365 colorder - 1 AS DAYS from master..syscolumns 
    WHERE id = -519536829 order by colorder) a

WHERE datediff(dd,dateadd(dd,DAYS,'2013-09-07 00:00:00'),'2013-09-13 00:00:00' ) >= 0 
    AND  dateadd(dd,DAYS,'2013-09-07 00:00:00') <=  '2013-09-13 00:00:00'  
    SELECT * FROM #TEMP1


评论

您能否提供示例说明？

–史蒂夫·威斯布鲁克
13-10-4在13:54

#20 楼

在这里可以找到答案
如何列出两个日期之间的所有日期

Create Procedure SelectDates(@fromDate Date, @toDate Date)
AS
BEGIN
    SELECT DATEADD(DAY,number,@fromDate) [Date]
    FROM master..spt_values
    WHERE type = 'P'
    AND DATEADD(DAY,number,@fromDate) < @toDate

END


评论

由于几个原因，这不是一个好答案。 1：主表并非始终可用。 2：该表仅与数据库中的项目数一样长。如果小于实际答案，则此过程的返回列表将不正确。 3：答案或多或少是使用系统表的数字表。

–丹·阿特金森
2014年6月23日10:07


#21 楼

DECLARE @StartDate DATE = '2017-09-13',         @EndDate DATE = '2017-09-16'

SELECT date  FROM (   SELECT DATE = DATEADD(DAY, rn - 1, @StartDate)   FROM    (
    SELECT TOP (DATEDIFF(DAY, @StartDate, DATEADD(DAY,1,@EndDate)))
      rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    ORDER BY s1.[object_id]   ) AS x ) AS y


结果：

2017-09-13

2017-09-14

2017-09-15

2017-09-16