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

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

是否有函数或查询将返回两个日期之间的天数列表？例如，假设有一个名为 ExplordeDates 的函数:

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 的答案，因为它是最快的，尽管数字表解决方案(KM 和 StingyJack 在他们的答案中都使用过)是我最喜欢的。Rob Farley 快了三分之二。

更新2

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

185551 次浏览
小开

一些想法:

如果您需要列表日期来遍历它们，您可以有一个 Start Date 和 Day Count 参数，并在创建日期并使用它的同时执行 while 循环？

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

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

小开

我是一个 Oracle 家伙，但我相信 MS SQL Server 支持连接条款:

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

对偶只是一个 Oracle 附带的“虚拟”表(它包含1行，单个列的值为“虚拟”)。

小开

在使用我的函数之前，您需要设置一个“ 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

功能如下:

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

产出:

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)
小开

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

例子:

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
小开
最佳答案

试试这样:

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;

编辑(接受后) :

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

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 的函数创建爆炸日期函数，因为查询优化器根本无法简化查询。

小开

绝对是一个数字表，但是如果你真的需要性能，你可能想使用 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

现在您已经有了一个日期表，您可以使用一个函数(非 PROC) ，如 KM 来获取它们的表。

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
小开

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

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;

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

小开

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

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
小开

在 sql 服务器中，这几行代码就是这个问题的简单答案。

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)
小开

这正是你想要的，从 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;
小开 
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 上，但是如果您将这个表换成一个虚拟表，那么这个工作就可以了。

小开 
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
小开

答案就在这里 如何列出两个日期之间的所有日期

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
小开

虽然有点晚了，但我很喜欢这个解决方案。

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
)
小开 
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
小开

六个半打的另一个，另一种方法假设

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;
小开 
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;
小开

如果你想打印年份从一个特定的年份到当前的日期。只是修改了已接受的答案。

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)
小开

此查询适用于 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 子句仅筛选所需范围内的日期。注意，我们在这里使用了列名，因为 SQLServer 不接受 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;
小开

如果你处在像我这样的情况下，你的 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'
小开 
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

提交答案