Months between two dates
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)
I've modified Jamiec's answer to output the Last day of the month as well.
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
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