Month - 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 2000 Forums

SQL Server Development (2000)

Month

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2002-01-21 : 16:59:26

Is there a way to list the months (Numbers), between a start and an end date?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 17:07:10

What do you mean?

Are the dates always in the same year?

select m.i
from
(select i = 1 union select 2 union select 3 union .... union select 12) as m
where m.i between datepart(mm,@start) and datepart(mm,@end)

You could put all the numbers in a temp table if you wish.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Scott
Posting Yak Master

145 Posts

Posted - 2002-01-22 : 01:16:42

eg: Start = 20011001 End = 20010530

result
month | Year
-------------
10 | 2001
11 | 2001
12 | 2001
1 | 2002
2 | 2002
3 | 2002
4 | 2002
5 | 2002

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-22 : 01:23:02

easiest way probably

create table #a (d datetime)
select @start = convert(varchar(6),@start,112) + '01'
while @start < = @end
begin
insert #a select @start
dateadd(mm,1,@start)
end

select datepart(mm,d), datepart(yy,d)
from #a
order by d

drop table #a

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-22 : 01:33:59

yep and scott, it'll work even better if your start date is earlier than your end date

I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Scott
Posting Yak Master

145 Posts

Posted - 2002-01-22 : 01:44:13

Thanks, one small syntax error:

declare @Start smalldatetime
declare @End smalldatetime
select @Start = '20010101'
select @End = '20021001'

set dateformat dmy

create table #a (d datetime)
While @start < = @end
begin
insert #a select @start
select @start = dateadd(mm,1,@start)
end

select datepart(mm,d), datepart(yy,d)
from #a
order by d

drop table #a

Edited by - scott on 01/22/2002 01:58:39

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-22 : 01:48:48

That's why I always put the increment at the beginning of the loop - except this time to proove it's a good idea.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources