Months between two dates

Question

Is it possible to get month names between two dates in SQl

ie, 2011-05-01 And 2011-08-01 are the inputs I just want the output as

------------ Month ------------ May June July August 

If any body knows the query please share.

Answer 1

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) 

Answer 2

You can do this with a recursive CTE, by building up a table of dates, and getting the month name from each:

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