t-sql获取介于2个日期之间的所有日期
t-sql获取介于2个日期之间的所有日期
提问于 2010-10-16 05:05:07
可能重复：
Getting Dates between a range of dates
假设我有两个日期(只有日期部分，没有时间)，我想获取这两个日期之间的所有日期，并将它们插入到一个表中。有没有一种简单的方法来使用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回答 4
Stack Overflow用户
回答已采纳
发布于 2010-10-16 05:09:25
假设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 to format as you like。
使用开始和结束参数：
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 sStack Overflow用户
发布于 2010-10-16 05:09:32
你需要一个数字表格。如果你没有一个永久的，这是生成一个的a more efficient方式，而不是使用一个递归的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)Stack Overflow用户
发布于 2010-10-16 05:09:55
我刚刚做了这样的事情：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/3946121
复制相关文章
相似问题
腾讯云开发者
