T-SQL获取两个日期之间的所有日期

Question

T-SQL获取两个日期之间的所有日期

19

可能是重复问题：
获取日期范围内的所有日期

假设我有两个日期（只有日期部分，没有时间），我想得到这两个日期之间的所有日期（包括这两个日期），然后将它们插入到表格中。是否有一种简单的 SQL 语句实现它（即不使用循环）？

Ex:
Date1: 2010-12-01
Date2: 2010-12-04

Table should have following dates:
2010-12-01, 2010-12-02, 2010-12-03, 2010-12-04

- dcp

4个回答

20

你需要一个数字表。如果你没有永久性的数字表，这是一种更有效的生成方法，而不是使用递归CTE。但只要从缓存区读取就可以了，永久性的数字表会更有效。

DECLARE @D1 DATE = '2010-12-01'
DECLARE @D2 DATE = '2010-12-04'

;WITH 
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L4)
SELECT DATEADD(day,i-1,@D1)
 FROM Nums where i <= 1+DATEDIFF(day,@D1,@D2)

- Martin Smith

5

这应该标记为答案，而不是递归CTE。递归是一个循环操作，在SQL中速度较慢。请注意，Itzik的原始示例使用TOP来限制跨连接的深度。 "Where i <="无法停止交叉连接，请参阅他的文章：http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers - Davos

4

将“Select”部分更改为：SELECT TOP (1+DATEDIFF(day,@D1,@D2)) DATEADD(day,i-1,@D1) FROM Nums - Davos

2

这绝对是最好的答案，不会遇到递归问题。 - Steve Sether

嗨@martin-smith，非常棒的答案！请详细解释上述逻辑。了解它会很棒。 - vCillusion

@vCillusion，解释在sqlmag链接中。本质上，交叉连接联合创建了一堆“1”（65536个，再添加另一个L5以获得更多），然后ROW_NUMBER()为它们提供了索引。 - freedomn-m

6

我刚刚做了类似这样的事情：

declare @dt datetime = '2010-12-01'
declare @dtEnd datetime = '2010-12-04'

WHILE (@dt < @dtEnd) BEGIN
    insert into table(datefield)
        values(@dt)
    SET @dt = DATEADD(day, 1, @dt)
END

- NotMe

谢谢，但其中一个要求是不使用循环（请参见原始问题）。 - dcp

2

重复的问题

获取两个日期之间的所有日期

DECLARE @DateFrom smalldatetime, @DateTo smalldatetime;
SET @DateFrom='20000101';
SET @DateTo='20081231';
-------------------------------
WITH T(date)
AS
( 
SELECT @DateFrom 
UNION ALL
SELECT DateAdd(day,1,T.date) FROM T WHERE T.date < @DateTo
)
SELECT date FROM T OPTION (MAXRECURSION 32767);

- SDReyes

1

TSQL是SQL Server或Sybase，里面没有提供Oracle / PLSQL（我所看到的），但直到11gR2，Oracle才支持递归WITH。 - OMG Ponies

顺便说一下，他们提供许多解决方案之一是面向Oracle的 :) +1。谢谢！ - SDReyes

比上面第一个（被接受的）解决方案更长，但更直观。 - yonsk

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

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

- OMG Ponies · Accepted Answer

假设使用SQL Server 2005+，可以使用递归查询:

WITH sample AS (
  SELECT CAST('2010-12-01' AS DATETIME) AS dt
  UNION ALL
  SELECT DATEADD(dd, 1, dt)
    FROM sample s
   WHERE DATEADD(dd, 1, dt) <= CAST('2010-12-04' AS DATETIME))
SELECT * 
  FROM sample

返回值：

 dt
 ---------
 2010-12-01 00:00:00.000
 2010-12-02 00:00:00.000
 2010-12-03 00:00:00.000
 2010-12-04 00:00:00.000

使用CAST/CONVERT按您喜欢的格式进行格式化。

使用参数来指定起始和结束：

INSERT INTO dbo.YOUR_TABLE
  (datetime_column)
WITH sample AS (
    SELECT @start_date AS dt
    UNION ALL
    SELECT DATEADD(dd, 1, dt)
      FROM sample s
     WHERE DATEADD(dd, 1, dt) <= @end_date)
SELECT s.dt
  FROM sample s