Sql months

johnkainn
johnkainn used Ask the Experts™
on
I am using SQL 2008. How do I return a list of all months between startdate(DateTime) and endate(DateTime).
For example March,April, May
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
declare @startDate datetime
declare @endDate datetime
set @startDate = '20100504'
set @endDate = '20100904'

;with dates([Month], [MonthName]) as
(
select dateadd(month, datediff(month, 0, @startdate), 0), datename(month, @startdate)
union all
select dateadd(month, 1, [Month]), datename(month, dateadd(month, 1, [Month]))
from dates
where datediff(month, dateadd(month, 1, [Month]), @endDate) >= 0
)
select * from dates
try this also:


declare @startDate datetime
declare @endDate datetime

set @startDate = '1-Oct-2010'
set @endDate = '15-Nov-2010'

create table #temp
(
	Month_Num int,
	Month_Name varchar(20)
)
while @startDate < @endDate
begin

insert into #temp(Month_Num, Month_Name) VALUES (month(@startDate), datename(month, @startDate))
set @startDate = dateAdd(m, 1, @startDate)
end

select distinct Month_Num, Month_Name from #temp ORDER BY Month_Num

drop table #temp

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial