I WANT TO DISPLAY ALL THE MONTHS BETWEEN 2 DATES FROM TABLE

Hi
My requirement is to display names all the months between two dates
from the table.
I have lots of START and STOP dates available in my table and I need
to display names of all the months including Start and Stop months.
How can I do that, with function or with some loop.
My database is SQL Server 2000 .
ThanksSee the DATENAME() function and SELECT DISTINCT.
Edmund
<akpatelrs@.googlemail.com> wrote in message
news:1149518723.100427.10020@.f6g2000cwb.googlegroups.com...
> Hi
> My requirement is to display names all the months between two dates
> from the table.
> I have lots of START and STOP dates available in my table and I need
> to display names of all the months including Start and Stop months.
> How can I do that, with function or with some loop.
> My database is SQL Server 2000 .
> Thanks
>|||Edmund,
Here is a way to display the month names between @.start and @.stop
for two variables. You should be able to adapt this to your needs.
It requires a table containing the integers from 0 on up.
declare @.start datetime set @.start = '20050323'
declare @.stop datetime set @.stop = '20070223'
select
datename(month,dateadd(month,datediff(mo
nth,0,@.start)+n,0)) as MonthName
from Numbers1000
where n <= datediff(month,@.start,@.stop)
order by n
Here's how to generate the table of 1000 integers (you probably don't
need more)
create table Numbers1000(
n int primary key
)
declare @.a int set @.a = 0
while @.a < 1000 begin
insert into Numbers1000 values (@.a)
set @.a = @.a + 1
end
Steve Kass
Drew University
Edmund wrote:

>See the DATENAME() function and SELECT DISTINCT.
>Edmund
><akpatelrs@.googlemail.com> wrote in message
>news:1149518723.100427.10020@.f6g2000cwb.googlegroups.com...
>
>
>|||Try this
declare @.date1 datetime,@.date2 datetime,@.str varchar(1000)
set @.str=''
set @.date1 = dateadd(mm,-4,getdate()) /* set it as yr start date */
set @.date2 = dateadd(mm,10,getdate()) /* set it as yr end date */
while datediff(mm,@.date1,@.date2)>0
begin
-- display month
set @.str= @.str+datename(mm,@.date1)+', '
-- decrease date
set @.date1 = dateadd(mm,1,@.date1)
end
declare @.strlen int
set @.strlen = len(@.str)
if @.strlen>1
select left(@.str,len(@.str)-2) as month_name
else
select @.str
hth
Peter
<akpatelrs@.googlemail.com> wrote in message
news:1149518723.100427.10020@.f6g2000cwb.googlegroups.com...
> Hi
> My requirement is to display names all the months between two dates
> from the table.
> I have lots of START and STOP dates available in my table and I need
> to display names of all the months including Start and Stop months.
> How can I do that, with function or with some loop.
> My database is SQL Server 2000 .
> Thanks
>|||akpatelrs@.googlemail.com wrote:
> Hi
> My requirement is to display names all the months between two dates
> from the table.
> I have lots of START and STOP dates available in my table and I need
> to display names of all the months including Start and Stop months.
> How can I do that, with function or with some loop.
> My database is SQL Server 2000 .
> Thanks
Here is data from my table. For instance, for first start n stop date,
i need to display JAN,FEB like wise for second MAR,APR,MAY . This way
for all Start and Stop date from the table.
StartDate
StopDate
2007-01-01 00:00:00.000 2007-02-17
00:00:00.000
2006-03-01 13:38:46.397 2006-05-01
13:38:46.397
2006-05-19 03:18:57.060 2006-05-19
03:18:57.060|||You have a few suggestions already. Let me put my weight behind Steve's use
of an auxiliary table of numbers. There are all kinds of uses for it, it
doesn't take up much space, and it's ideal for situations like that which
you've got into. Populate it once, and then forget it's there. Just make
sure it's large enough - there may be times when you want it to be larger
than 1000. It's easy to top-up of course, but you don't want to find that
you discover it's too small when a client's report comes out short.
Rob
<akpatelrs@.googlemail.com> wrote in message
news:1149518723.100427.10020@.f6g2000cwb.googlegroups.com...
> Hi
> My requirement is to display names all the months between two dates
> from the table.
> I have lots of START and STOP dates available in my table and I need
> to display names of all the months including Start and Stop months.
> How can I do that, with function or with some loop.
> My database is SQL Server 2000 .
> Thanks
>