SQL 在日期范围之间生成日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7824831/
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
Generate Dates between date ranges
提问by Nate Pet
I need to populate a table that will store the date ranges between 2 given dates: 09/01/11 - 10/10/11
我需要填充一个表格来存储两个给定日期之间的日期范围：09/01/11 - 10/10/11
So in this case the table would start from 09/01/11 and store each day till it got to 10/10/11 I was wondering if there was a slick way of doing this in SQL Server - I am currently using SQL Server 2008. Thanks
因此，在这种情况下，该表将从 11 年 9 月 1 日开始并每天存储，直到 10 年 10 月 11 日我想知道在 SQL Server 中是否有一种巧妙的方法 - 我目前正在使用 SQL Server 2008 。 谢谢
回答by Stuart Ainsworth
Easy on SQL 2005+; easier if you have a numbers or tally table. I faked it below:
易于使用 SQL 2005+；如果您有数字或计数表，则更容易。我在下面伪造了它：
DECLARE @StartDate DATE = '20110901'
, @EndDate DATE = '20111001'
SELECT DATEADD(DAY, nbr - 1, @StartDate)
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
FROM sys.columns c
) nbrs
WHERE nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
If you have a tally table, replace the subquery with the table. No recursion.
如果您有一个计数表，请用该表替换子查询。没有递归。
回答by Abe Miessler
Try this if you are using SQL Server 2005 or newer:
如果您使用的是 SQL Server 2005 或更新版本，请尝试此操作：
WITH Dates AS (
SELECT
[Date] = CONVERT(DATETIME,'09/01/2011')
UNION ALL SELECT
[Date] = DATEADD(DAY, 1, [Date])
FROM
Dates
WHERE
Date < '10/10/2011'
) SELECT
[Date]
FROM
Dates
OPTION (MAXRECURSION 45)
A good example of cool stuff you can do with a CTE.
你可以用 CTE 做的很酷的事情的一个很好的例子。
回答by sll
-- Declarations
--声明
DECLARE @dates TABLE(dt datetime)
DECLARE @dateFrom datetime
DECLARE @dateTo datetime
SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'
-- Query:
--查询：
WHILE(@dateFrom < @dateTo)
BEGIN
SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
INSERT INTO @dates
SELECT @dateFrom
END
-- Output
--输出
SELECT * FROM @dates
回答by sken130
Here is a solution that does not require recursion, and at the same time, this table-valued function is re-usable in many queries without the need to repeat the declaration of boilerplate variables again. This is the only alternative, for those who don't want recursion.
这里有一个不需要递归的解决方案，同时这个表值函数可以在很多查询中重用，不需要再次重复声明样板变量。对于那些不想递归的人来说，这是唯一的选择。
Create this simple function:
创建这个简单的函数：
CREATE FUNCTION [dbo].[GenerateDateRange]
(@StartDate AS DATE,
@EndDate AS DATE,
@Interval AS INT
)
RETURNS @Dates TABLE(DateValue DATE)
AS
BEGIN
DECLARE @CUR_DATE DATE
SET @CUR_DATE = @StartDate
WHILE @CUR_DATE <= @EndDate BEGIN
INSERT INTO @Dates VALUES(@CUR_DATE)
SET @CUR_DATE = DATEADD(DAY, @Interval, @CUR_DATE)
END
RETURN;
END;
And then select by:
然后选择：
select *
from dbo.GenerateDateRange('2017-01-03', '2017-12-01', 1)
回答by JonH
Use MVJ's F_TABLE_DATE function, it is purely awesome:
使用 MVJ 的 F_TABLE_DATE 函数，简直太棒了：
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Once you implement this just pass in start and end date and you can insert all dates between.
实现后，只需传入开始日期和结束日期，您就可以在两者之间插入所有日期。
回答by SQL RV
I realize that this is an old thread, but I have to admit my dismay at the overabundance of recursive and looping solutions given here. I wonder just how many folks realize that recursion is nothing more than a very expensive loop? I understand the desire to create a Table-Valued Function, but I suggest that the following is far more efficient as it is set-based, without looping, recursion, or repeated single insert statements:
我意识到这是一个旧线程，但我不得不承认我对这里给出的过多的递归和循环解决方案感到沮丧。我想知道有多少人意识到递归只不过是一个非常昂贵的循环？我理解创建表值函数的愿望，但我建议以下更有效，因为它是基于集合的，没有循环、递归或重复的单个插入语句：
CREATE FUNCTION dbo.GenerateDateRange(@StartDate AS DATE, @EndDate AS DATE)
RETURNS TABLE WITH SCHEMABINDING AS
WITH e1(n) AS (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(n)) -- 16 records
,e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b) -- 16^2 or 256 records (16*16)
,cteTally(n) AS (SELECT ROW_NUMBER() over (ORDER BY 1) AS n FROM e2 a CROSS JOIN e2 b) -- 16^4 or 65,536 records (256*256)
SELECT DATEADD(DAY, n-1, @StartDate)
FROM cteTally
WHERE n <= DATEDIFF(DAY, @StartDate, @EndDate) + 1;
GO
回答by DanielG
This is an old thread, but in case it helps anyone, this is what I use in modern versions of SQL Server that support CTE's. This also gives you the Day of the Week and it can be tweaked to give other values you may need (i.e. Quarter, Month, etc.).
这是一个旧线程，但如果它对任何人有帮助，这就是我在支持 CTE 的现代版本的 SQL Server 中使用的。这也为您提供了星期几，并且可以对其进行调整以提供您可能需要的其他值（即季度、月份等）。
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '1/1/2020'
SET @EndDate = '12/31/2020'
DECLARE @DayTable Table(theDate date, theDayOfWeek nvarchar(50));
WITH DayTable AS (SELECT CAST(@StartDate AS DATETIME) theDate, DATENAME(dw, @StartDate) theDayOfWeek UNION ALL SELECT DATEADD(dd, 1, theDate), DATENAME(dw,DATEADD(dd, 1, theDate)) FROM DayTable s WHERE DATEADD(dd, 1, theDate) <= CAST(@EndDate AS DATETIME))
INSERT INTO @DayTable(theDate, theDayOfWeek) SELECT theDate, theDayOfWeek FROM DayTable OPTION (MAXRECURSION 365);
SELECT * FROM @DayTable
回答by Lars Blumberg
If for some reason you can't declarevariables, such as when using derived tablesin Looker, you can go like this:
如果由于某种原因你不能declare变量，例如在Looker 中使用派生表时，你可以这样：
select
dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
from (
select row_number() over (order by c.object_id) as nbr from sys.columns c
) nbrs
where
nbr - 1 <= datediff(
day,
convert(date, '2017-01-01'),
convert(date, '2018-12-31')
)
By the way, this is how your date seriesview could look like in LookerML:
顺便说一下，这就是您的日期系列视图在 LookerML 中的样子：
view: date_series {
derived_table: {
sql:
select
dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
from (
select row_number() over (order by c.object_id) as nbr from sys.columns c
) nbrs
where
nbr - 1 <= datediff(day, convert(date, '2017-01-01'), convert(date, '2018-12-31')) ;;
}
dimension: date {
primary_key: yes
type: date
sql: ${TABLE}.d ;;
}
}
回答by Mukehp
CREATE table #ProductSales (ProjectID Int, ProjectName varchar(100), TotalBillableFees Money, StartDate Date, EndDate Date, DataDate Date)
Insert into #ProductSales
Values
(373104,'Product Sales - Flex Creation Test',40000.00,'2019-04-01','2020-06-01','2019-08-01'),
(375111,'Product Sales - SMART',40000.00,'2019-04-01','2019-09-01','2019-08-01')
;WITH Dates AS (
SELECT ProjectiD
,Convert(decimal(10,2),TotalBillableFees/IIF(DATEDIFF(MONTH,StartDate,EndDate)=0,1,DATEDIFF(MONTH,StartDate,EndDate))) AS BillableFeesPerMonths,EndDate
,[Date] = CONVERT(DATETIME,EOMONTH(StartDate))
FROM #ProductSales
UNION ALL SELECT ProjectiD,BillableFeesPerMonths,EndDate,
[Date] = DATEADD(MONTH, 1, [Date])
FROM
Dates
WHERE
Date < EOMONTH(EndDate)
) SELECT ProjectID,BillableFeesPerMonths,
CAST([Date] as Date) Date
FROM
Dates
OPTION (MAXRECURSION 45)
回答by Richard Griffiths
Using @Abe Miesler's answer, for other's convenience I built it into a TVF for SQL Server 2008 onwards. It may help others - I had to find a way to include the CTE inside the TVF!
使用@Abe Miesler 的回答，为了其他人的方便，我将它内置到 SQL Server 2008 以后的 TVF 中。它可能对其他人有所帮助 - 我必须找到一种方法将 CTE 包含在 TVF 中！
--Generate a range of dates with interval option, courtesy of Abe Miessler for the core query here!
ALTER FUNCTION [dbo].[DateRange]
(@startDate AS DATE,
@EndDate AS DATE,
@interval AS INT
)
RETURNS @Dates TABLE(dateValue DATE)
AS
BEGIN
WITH Dates
AS (
SELECT [Date] = CONVERT( DATETIME, @startDate)
UNION ALL
SELECT [Date] = DATEADD(DAY, ISNULL(@interval, 1), [Date])
FROM Dates
WHERE Date < @EndDate)
INSERT INTO @Dates
SELECT [Date]
FROM Dates
OPTION(MAXRECURSION 900);
RETURN;
END;
