试试这个：

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

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);

结果：

MonthName
------------------------------
May
June
July
August

(4 row(s) affected)

您可以使用递归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

我已经修改了Jamiec's answer以输出该月的最后一天。

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

输出如下：

MonthName   MonthNumber LastDayOfMonth  MonthYear
May         5           31              2014
June        6           30              2014
July        7           31              2014
August      8           31              2014
September   9           30              2014

受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

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

Well, @bogdhan sahlean has given a nice set based solution, but restricts the values upto 2048 considering the datatype date and datetime2 which the range for year is 0001-01-01 to 9999-12-31 , From MSDN
Date range 0001-01-01 through 9999-12-31
January 1,1 CE through December 31, 9999 CE
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 :)
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.
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
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 0 to start the numbers from 0 th position
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.
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.
The performance between tally table, calendar table and itzik number table may vary slightly but works like charm with all date range you supply.

创建数据库功能如下

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

然后使用函数生成一个日期范围

SELECT * from dbo.DateRange('M','1953-01-01','2019-01-01')

如果我们想要格式化输出，我们可以将结果存储在表变量中，如下例所示：

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

我们可以根据需要进行更改

OPTION (MAXRECURSION 3660)