SQL 用介于 2 个日期参数之间（包括 2 个日期参数）的日期填充临时表的最简单方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7812986/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Easiest way to populate a temp table with dates between and including 2 date parameters
提问by mameesh
What is the easiest way to populate a temp table with dates including and between 2 date parameters. I only need the 1st day of the month dates.
用包含和介于 2 个日期参数之间的日期填充临时表的最简单方法是什么。我只需要当月日期的第一天。
So for example if @StartDate = '2011-01-01' and @EndDate = '2011-08-01'
例如，如果@StartDate = '2011-01-01' 和 @EndDate = '2011-08-01'
Then I want this returned in the table
然后我希望在表中返回
2011-01-01
2011-02-01
2011-03-01
2011-04-01
2011-05-01
2011-06-01
2011-07-01
2011-08-01
回答by GilM
This works even if the @StartDate is not the first of the month. I'm assuming that if it's not the start of the month, you want to begin with the first of the next month. Otherwise remove the +1.:
即使 @StartDate 不是本月的第一天，这也有效。我假设如果不是月初，您希望从下个月的第一天开始。否则删除 +1。：
;WITH cte AS (
SELECT CASE WHEN DATEPART(Day,@StartDate) = 1 THEN @StartDate
ELSE DATEADD(Month,DATEDIFF(Month,0,@StartDate)+1,0) END AS myDate
UNION ALL
SELECT DATEADD(Month,1,myDate)
FROM cte
WHERE DATEADD(Month,1,myDate) <= @EndDate
)
SELECT myDate
FROM cte
OPTION (MAXRECURSION 0)
回答by Nitin Vartak
declare @StartDate date = '2014-01-01';
declare @EndDate date = '2014-05-05';
;WITH cte AS (
SELECT @StartDate AS myDate
UNION ALL
SELECT DATEADD(day,1,myDate) as myDate
FROM cte
WHERE DATEADD(day,1,myDate) <= @EndDate
)
SELECT myDate
FROM cte
OPTION (MAXRECURSION 0)
回答by Icarus
declare @StartDate datetime
declare @EndDate datetime
select @StartDate = '2011-01-01' , @EndDate = '2011-08-01'
select @StartDate= @StartDate-(DATEPART(DD,@StartDate)-1)
declare @temp table
(
TheDate datetime
)
while (@StartDate<=@EndDate)
begin
insert into @temp
values (@StartDate )
select @StartDate=DATEADD(MM,1,@StartDate)
end
select * from @temp
Works even if the @StartDate is not the first day of the month by going back to the initial day of the month of StartDate
即使 @StartDate 不是该月的第一天，也可以通过返回到 StartDate 月份的第一天来工作
回答by mhd ghaith Maarawi
this is tested in SQL 2008 R2
这是在 SQL 2008 R2 中测试的
Declare @StartDate datetime = '2015-03-01'
Declare @EndDate datetime = '2015-03-31'
declare @temp Table
(
DayDate datetime
);
WHILE @StartDate <= @EndDate
begin
INSERT INTO @temp (DayDate) VALUES (@StartDate);
SET @StartDate = Dateadd(Day,1, @StartDate);
end ;
select * from @temp
Result:
结果：
DayDate
-----------------------
2015-03-01 00:00:00.000
2015-03-02 00:00:00.000
2015-03-03 00:00:00.000
2015-03-04 00:00:00.000
...
回答by Richard
Interestingly, it is faster to create from enumerated data as per this article.
有趣的是，根据本文，从枚举数据创建更快。
DECLARE @StartDate DATE = '10001201';
DECLARE @EndDate DATE = '20000101';
DECLARE @dim TABLE ([date] DATE)
INSERT @dim([date])
SELECT d
FROM
(
SELECT
d = DATEADD(DAY, rn - 1, @StartDate)
FROM
(
SELECT TOP (DATEDIFF(DAY, @StartDate, @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;
On my machine, it's around 60% faster with large date ranges. The recursion method can populate 2000 years worth of data in around 3 seconds though, and looks a lot nicer, so I don't really recommend this method just for incrementing days.
在我的机器上，大日期范围的速度提高了大约 60%。递归方法可以在大约 3 秒内填充 2000 年的数据，并且看起来更好，所以我真的不推荐这种方法只用于增加天数。
回答by Damien Astolfi
Correction for null dates:
更正空日期：
IF OBJECT_ID('tempdb..#dim') IS NOT NULL
DROP TABLE #dim
CREATE TABLE #dim ([date] DATE)
if not @Begin_Date is null and not @End_Date is null
begin
INSERT #dim([date])
SELECT d
FROM(
SELECT
d = DATEADD(DAY, rn - 1, @Begin_Date)
FROM
(
SELECT TOP (DATEDIFF(DAY, @Begin_Date, @End_Date))
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;
end
回答by Bogdan Sahlean
Solution:
解决方案：
DECLARE @StartDate DATETIME
,@EndDate DATETIME;
SELECT @StartDate = '20110105'
,@EndDate = '20110815';
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, v.number, @StartDate)), 0) AS FirstDay
--or Andriy M suggestion:
--SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate) + v.number, 0) AS FirstDay
INTO #Results
FROM master.dbo.spt_values v
WHERE v.type = 'P'
AND DATEDIFF(MONTH, @StartDate, @EndDate) >= v.number;
SELECT *
FROM #Results;
DROP TABLE #Results;
Results:
结果：
FirstDay
-----------------------
2011-01-01 00:00:00.000
2011-02-01 00:00:00.000
2011-03-01 00:00:00.000
2011-04-01 00:00:00.000
2011-05-01 00:00:00.000
2011-06-01 00:00:00.000
2011-07-01 00:00:00.000
2011-08-01 00:00:00.000
回答by Ji?í Sedlák
CREATE TABLE #t (d DATE)
INSERT INTO #t SELECT GETDATE()
GO
INSERT #t SELECT DATEADD(DAY, -1, MIN(d)) FROM #t
GO 10
