SQL: list of Month Year / list of Dates between two dates

If you need a complete list of month-year as well as dates between two datetimes in SQL, you can use the scripts below.

DATE list between two datetimes(including both the dates):

      DECLARE @startDate DATE = '2014-04-28', @endDate DATE  = '2014-05-06'
              
                 ;      WITH CTE AS  
                 (      
                     SELECT CONVERT(DATE, @startDate) AS  Dates  
              
                     UNION ALL  
              
                     SELECT DATEADD(D, 1, Dates)  
                     FROM CTE  
                     WHERE CONVERT(DATE, Dates) <= CONVERT(DATE, DATEADD(d, -1, @endDate))  
                 )      
                 SELECT * FROM CTE  
                 OPTION (maxrecursion 0)  

MONTH-YEAR list between two datetimes(including the months of both the dates):

      DECLARE @startDate DATE = '2014-01-28', @endDate DATE  = '2014-05-06'
              
                 ;      WITH CTE AS  
                 (      
                     SELECT CONVERT(DATE, @startDate) AS  Dates  
              
                     UNION ALL  
              
                     SELECT DATEADD(MONTH, 1, Dates)  
                     FROM CTE  
                     WHERE CONVERT(DATE, Dates) <= CONVERT(DATE, @endDate)  
                 )      
                 SELECT DATENAME(MONTH,Dates) + ' ' + DATENAME(YEAR, Dates) FROM  CTE  
                 OPTION (maxrecursion 0)  

Using the recursive capability of CTE, it becomes easier else we might think of using WHILE loop or Temp table/ Table variables to store the values temporarily, which will be much more lengthier.