SQL 两个日期之间的月份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7885851/
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
Months between two dates
提问by Nithesh Narayanan
Is it possible to get month namesbetween two dates in SQl
是否有可能month names在两个日期之间SQl
ie,
2011-05-01And 2011-08-01are the inputs
I just want the output as
也就是说，
2011-05-01而且2011-08-01是输入我只想输出
------------
Month
------------
May
June
July
August
If any body knows the query please share.
如果任何机构知道查询，请分享。
回答by Bogdan Sahlean
DECLARE @StartDate DATETIME,
@EndDate DATETIME;
SELECT @StartDate = '20110501'
,@EndDate = '20110801';
SELECT DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM master.dbo.spt_values x
WHERE x.type = 'P'
AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);
Results:
结果：
MonthName
------------------------------
May
June
July
August
(4 row(s) affected)
回答by Jamiec
You can do this with a recursive CTE, by building up a table of dates, and getting the month name from each:
您可以使用递归 CTE 来完成此操作，方法是建立一个日期表，并从每个日期表中获取月份名称：
declare @start DATE = '2011-05-01'
declare @end DATE = '2011-08-01'
;with months (date)
AS
(
SELECT @start
UNION ALL
SELECT DATEADD(month,1,date)
from months
where DATEADD(month,1,date)<=@end
)
select Datename(month,date) from months
回答by Elan Hasson
I've modified Jamiec's answerto output the Last day of the month as well.
我已经修改了Jamiec 的答案以输出该月的最后一天。
declare @start DATE = '2014-05-01'
declare @end DATE = getdate()
;with months (date)
AS
(
SELECT @start
UNION ALL
SELECT DATEADD(month, 1, date)
from months
where DATEADD(month, 1, date) < @end
)
select [MonthName] = DATENAME(mm, date),
[MonthNumber] = DATEPART(mm, date),
[LastDayOfMonth] = DATEPART(dd, EOMONTH(date)),
[MonthYear] = DATEPART(yy, date)
from months
Which gives output:
这给出了输出：
MonthName MonthNumber LastDayOfMonth MonthYear
May 5 31 2014
June 6 30 2014
July 7 31 2014
August 8 31 2014
September 9 30 2014
回答by gotqn
Inspired by Jamiec's answer, but fixing issue with with from daybigger then to day:
受Jamiec's answer 的启发，但解决了从day更大到的问题day：
declare @start DATE
declare @end DATE
SELECT @start='2011-05-19' , @end='2011-08-15'
;with months (date)
AS
(
SELECT DATEADD(DAY,1,EOMONTH(@start,-1))
UNION ALL
SELECT DATEADD(month,1,date)
from months
where DATEADD(month,1,date) < EOMONTH(@end)
)
select Datename(month,date)
from months
回答by Sumesh
declare @start DATE = '2011-05-30'
declare @end DATE = '2011-06-10'
;with months (date)
AS
(
SELECT @start
UNION ALL
SELECT DATEADD(month,1,date)
from months
where DATEADD(month,1,date)<= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@end)+1,0))
)
select Datename(month,date) from months
回答by Dhruv Patel
Create Database function like below
创建如下所示的数据库函数
CREATE FUNCTION [dbo].[DateRange]
(@Identifier CHAR(1),@StartDate DATETIME,@EndDate DATETIME)
RETURNS @SelectedRange TABLE(Dates DATE) AS
BEGIN
;WITH cteRange (DateRange) AS (
SELECT @StartDate
UNION ALL
SELECT
CASE
WHEN Upper(@Identifier) = 'H' THEN DATEADD(hh, 1, DateRange)
WHEN Upper(@Identifier) = 'D' THEN DATEADD(dd, 1, DateRange)
WHEN Upper(@Identifier) = 'W' THEN DATEADD(ww, 1, DateRange)
WHEN Upper(@Identifier) = 'M' THEN DATEADD(mm, 1, DateRange)
WHEN Upper(@Identifier) = 'Y' THEN DATEADD(yy, 1, DateRange)
END
FROM cteRange
WHERE DateRange <=
CASE
WHEN Upper(@Identifier) = 'H' THEN DATEADD(hh, -1, @EndDate)
WHEN Upper(@Identifier) = 'D' THEN DATEADD(dd, -1, @EndDate)
WHEN Upper(@Identifier) = 'W' THEN DATEADD(ww, -1, @EndDate)
WHEN Upper(@Identifier) = 'M' THEN DATEADD(mm, -1, @EndDate)
WHEN Upper(@Identifier) = 'Y' THEN DATEADD(yy, -1, @EndDate)
END)
INSERT INTO @SelectedRange (Dates) SELECT DateRange FROM cteRange
OPTION (MAXRECURSION 3660);
RETURN
END
Then using the function we can generate a range of dates
然后使用该函数我们可以生成一个日期范围
SELECT * from dbo.DateRange('M','1953-01-01','2019-01-01')
If we want formatted output we can store the result in table variable like below example,
如果我们想要格式化输出，我们可以将结果存储在表变量中，如下例所示，
DECLARE @tblDateRange TABLE (AutoID INT IDENTITY(1,1),DateRange DATE)
INSERT INTO @tblDateRange SELECT * from dbo.DateRange('M','1953-01-01','2019-01-01')
SELECT
LEFT(DATENAME(MONTH,DateRange),3) [MonthYearValue],YEAR(DateRange) AS [Year]
FROM @tblDateRange
Based on our need we can change
根据我们的需要，我们可以改变
OPTION (MAXRECURSION 3660)
回答by Biju jose
Well,
@bogdhan sahlean has given a nice set based solution, but restricts the values upto 2048 considering the datatype dateand datetime2which the range for year is 0001-01-01to 9999-12-31, From MSDN
那么，@bogdhan sahlean给予了基于组漂亮的解决方案，但限制值高达2048考虑数据类型date和datetime2这对于今年的范围是0001-01-01到9999-12-31，从MSDN
Date range 0001-01-01 through 9999-12-31
January 1,1 CE through December 31, 9999 CE
日期范围 0001-01-01 到 9999-12-31
公元 1 月 1 日至公元 9999 年 12 月 31 日
even though this is the extreme case but worth knowing. Since what if one day someone is trying to project months more than 170 years :)
尽管这是极端情况但值得了解。如果有一天有人试图预测超过 170 年的时间会怎样:)
Even the most upvoted answers are not fulfilling some edge cases (when start date >end date will not show the month of end date, also, the recursive query fails after 100 executions by default). And also using recursive cte for iteration which is performance hog when used massively.
即使是最受好评的答案也不能满足某些边缘情况（当开始日期 > 结束日期不会显示结束日期的月份时，默认情况下递归查询在执行 100 次后失败）。并且还使用递归 cte 进行迭代，这在大量使用时会影响性能。
Now , a better solution (IMHO) is to use a calendar table or a tally table to generate the months between two dates. IF one can't create a table , there is better alternative to use Itzik ben Gans cascading CTE for generating numbers table.(here) Which is faster,No logical,physical reads,No worktable NADA
现在，更好的解决方案（恕我直言）是使用日历表或计数表来生成两个日期之间的月份。如果无法创建表，则有更好的替代方法可以使用 Itzik ben Gans 级联 CTE 来生成数字表。（这里）哪个更快，没有逻辑，物理读取，没有工作表 NADA
Here is the code
这是代码
DECLARE @start DATETIME2 = '00010101'
DECLARE @end DATETIME2 = '99991231'
;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
,Tally (n) AS (SELECT 0 UNION SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
SELECT DATENAME(YEAR,DATEADD(MONTH,N,@start)) AS [Year Part], DATENAME(MONTH,DATEADD(MONTH,n,@start)) AS [Month Part]
FROM Tally where N between 0 and DATEDIFF(mm,@start,@end)
ORDER BY n;
NB: I have added SELECT 0to start the numbers from 0 th position
注意：我添加SELECT 0了从第 0 个位置开始的数字
The performance shown in my PC is
在我的电脑中显示的性能是
Itzik Method
伊兹克法
(119988 row(s) affected)
SQL Server Execution Times: CPU time = 187 ms, elapsed time = 706 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
（119988 行受影响）
SQL Server 执行时间：CPU 时间 = 187 毫秒，已用时间 = 706 毫秒。SQL Server 解析和编译时间：CPU 时间 = 0 毫秒，已用时间 = 0 毫秒。
One of the recursive solution given here which takes a time of
这里给出的递归解决方案之一需要时间
(119988 row(s) affected) Table 'Worktable'. Scan count 2, logical reads 719923, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 890 ms, elapsed time = 1069 ms.
（119988 行受影响）表“工作表”。扫描计数 2，逻辑读 719923，物理读 0，预读 0，lob 逻辑读 0，lob 物理读 0，lob 预读 0。
SQL Server 执行时间：CPU 时间 = 890 毫秒，已用时间 = 1069 毫秒。
The performance between tally table, calendar table and itzik number table may vary slightly but works like charm with all date range you supply.
理货表、日历表和 itzik 数字表之间的性能可能略有不同，但在您提供的所有日期范围内都具有魅力。
回答by user2492359
Try this:
尝试这个：
declare
@sd date=getdate(),
@ld date='2016-01-01'
select
Datename(month,dateadd(month,number,GETDATE())),
number
from master.dbo.spt_values
where type='p'
and dateadd(month,number,GETDATE()) <= @ld
