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

Question

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

73

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

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

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

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

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

更新

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

越低越好

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

更新2

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

- Dan Atkinson

4

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

1

使用 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

显示剩余2条评论

21个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- computingfreak · Answer 1

如果你和我一样处于禁止使用存储过程和函数的情况下，而且你的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'

- Krish · Answer 2

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

- M T Head · Answer 3

-- ### 六分之一，半打。另一种方法假设使用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;

- KM. · Answer 4

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

- StingyJack · Answer 5

这绝对是一个数字表，但如果你真的需要性能，你可能想使用Mark Redman的CLR过程/程序集的想法。

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

/*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的函数（而不是过程）来获取它们的表格。

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

- Will Marcouiller · Answer 6

如果你想走一条更简单的路，也许这样做就可以了。

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;

但是临时表也是一个非常好的方法。您可能还应该考虑一个已填充的日历表。

- MNH · Answer 7

如果您想打印从特定年份开始到当前日期的年份，只需更改接受的答案即可。

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)

- Mr.D · Answer 8

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

结果：

- Chitta · Answer 9

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

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

- ASEEM · Answer 10

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