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

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

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

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:

这将返回一个列表，其中的值为:

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

我在想日历/数字表可能对我有帮助。

Update

更新

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's answer : 18%
罗伯·法利的回答是:18%
StingyJack's answer : 41%
StingyJack的回答是:41%
KM's answer : 41%
公里的回答是:41%

Lower is better

较低的好

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.

我接受了Rob Farley的答案，因为它是最快的，尽管数字表解决方案(KM和StingyJack在他们的答案中都用过)是我最喜欢的。罗伯·法利的速度快了三分之二。

Update 2

更新2

Alivia's answer is much more succinct. I have changed the accepted answer.

Alivia的回答要简洁得多。我已经改变了公认的答案。

21 个解决方案

#1

this few lines are the simple answer for this question in sql server.

这几行是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)

#2

Try something like this:

试试这样:

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

You then use:

然后使用:

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

Edited (after the acceptance):

编辑(验收后):

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

请注意……如果您已经有一个足够大的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
);

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

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

这些行将创建一个包含1M行的数字表……而且比一个一个地插入要快得多。

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.

您不应该使用包含开始和结束的函数创建爆炸日期函数，因为查询优化器根本无法简化查询。

#3

This does exactly what you want, modified from Will's earlier post. No need for helper tables or loops.

这正是你想要的，从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;

#4

I'm an oracle guy, but I believe MS SQL Server has support for the connect by clause:

我是oracle的人，但是我相信MS SQL Server支持connect by子句:

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

The output is:

的输出是:

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 is just a 'dummy' table that comes with oracle (it contains 1 row and the word 'dummy' as the value of the single column).

Dual只是oracle附带的一个“哑”表(它包含一行，单词“哑”作为单列的值)。

#5

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;

#6

A few ideas:

几个想法:

If you need the list dates in order to loop through them, you could have a Start Date and Day Count parameters and do a while loop whilst creating the date and using it?

如果您需要列表日期来循环它们，您可以有一个开始日期和日计数参数，并在创建日期和使用日期时做一个while循环?

Use C# CLR Stored Procedures and write the code in C#

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

Do this outside the database in code

在数据库之外的代码中这样做吗

#7

Would all these dates be in the database already or do you just want to know the days between the two dates? If it's the first you could use the BETWEEN or <= >= to find the dates between

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

EXAMPLE:

例子:

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

#8

All you have to do is just change the hard coded value in the code provided below

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

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

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

在使用我的函数之前，需要设置一个“helper”表，每个数据库只需要做一次:

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

here is the function:

这是功能:

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

use this:

用这个:

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

output:

输出:

Date
-----------------------
2010-01-01 00:00:00.000
2010-01-02 00:00:00.000
2010-01-03 00:00:00.000
2010-01-04 00:00:00.000
2010-01-05 00:00:00.000
2010-01-06 00:00:00.000
2010-01-07 00:00:00.000
2010-01-08 00:00:00.000
2010-01-09 00:00:00.000
2010-01-10 00:00:00.000
2010-01-11 00:00:00.000
2010-01-12 00:00:00.000
2010-01-13 00:00:00.000

(13 row(s) affected)

#10

Perhaps if you wish to go an easier way, this should do it.

也许如果你想走一条更容易的路，这条路应该可以走。

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;

But the temporary table is a very good approach also. Perhaps shall you also consider a populated calendar table.

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

#11

Definately a numbers table, though tyou may want to use Mark Redman's idea of a CLR proc/assembly if you really need the performance.

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

How to create the table of dates (and a super fast way to create a numbers table)

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

/*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

Now that you have a table of dates, you can use a function (NOT A PROC) like KM's to get the table of them.

现在您已经有了一个日期表，您可以使用一个类似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

#12

A Bit late to the party, but I like this solution quite a bit.

参加聚会有点晚，但我很喜欢这个解决办法。

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
            )

#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

-- ### Six of one half dozen of another. Another method assuming 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

In case you want to print years starting from a particular year till current date. Just altered the accepted answer.

如果你想打印年份，从某一年开始到现在。只是改变了公认的答案。

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

This query works on Microsoft SQL Server.

该查询适用于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;

Now let's look at how it works.

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

The inner query merely returns a list of integers from 0 to 9999. It will give us a range of 10,000 values for calculating dates. You can get more dates by adding rows for ten_thousands and hundred_thousands and so forth.

内部查询仅返回从0到9999的整数列表。它将为计算日期提供10,000个值的范围。您可以通过为ten_thousand和100,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;

This part converts the string to a date and adds a number to it from the inner query.

该部分将字符串转换为日期，并从内部查询向其添加数字。

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

Then we convert the result into the format you want. This is also the column name!

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

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

Next we extract only the distinct values and give the column name an alias of aDate.

接下来，我们只提取不同的值，并给列名一个别名aDate。

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

We use the where clause to filter in only dates within the range you want. Notice that we use the column name here since SQL Server does not accept the column alias, aDate, within the where clause.

我们使用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)

Lastly, we sort the results.

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

   order by aDate asc;

#17

if you're in a situation like me where procedures and functions are prohibited, and your sql user does not have permissions for insert, therefore insert not allowed, also "set/declare temporary variables like @c is not allowed", but you want to generate a list of dates in a specific period, say current year to do some aggregation, use this

如果你是像我这样的情况,过程和函数是被禁止的,和您的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

-1

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

The detail is on the table DUAL but if your exchange this table for a dummy table this works.

细节在表的对偶上，但是如果您将该表交换为一个虚拟表，这是可行的。

#19

-1

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

#20

-1

Answer is avialbe here How to list all dates between two dates

答案是avialbe如何列出两个日期之间的所有日期

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

#21

-1

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

Result:

结果:

#1