display all month names - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

display all month names

Author

Topic

headbuzz
Starting Member

11 Posts

Posted - 2010-06-03 : 12:23:16

I have two dates: subscriptionstartdate and subscriptionenddate. My goal is to display all the month names between and including these two dates. Suppose the dates are 1/2/2009 and 3/6/2009, I need to get the output as february, march,april,may,june. I used the following but it only returns as a broken response (multiple outputs, which I cannot use to bind any element in the front end).

declare @t as int
declare @i as int
declare @k as int
set @k=0
set @i=(select month(SubscriptionStartDate) from Subscriber where SubscriberID=10000002)
set @t=(select month(SubscriptionEndDate) from Subscriber where SubscriberID=10000002)
while @i<@t
begin
select datename(month,dateadd(month, month(SubscriptionStartDate) - 1+@k, 0)) as MonthName from Subscriber where SubscriberID=10000002
-
set @i=@i+1
set @k=@k+1
end

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-03 : 12:29:53

declare @startdate datetime
declare @enddate datetime

set @startdate = '1/2/2009'
set @endDate = '3/6/2009'

select datename(month,dateadd(month,number,@startdate))
from
master..Spt_Values spt
where
spt.[type] = 'P'

and spt.number <= datediff(month,@startdate,@enddate)

jim

Everyday I learn something that somebody else already knew

headbuzz
Starting Member

11 Posts

Posted - 2010-06-03 : 12:34:00

Wow!Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources