By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,512 Members | 3,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,512 IT Pros & Developers. It's quick & easy.

Way to create table of months?

P: n/a
Is there an way to create a table of months using SQL functions? If
"July" is entered for the beginning month, and "December" is entered
for the ending month, then I'd like to create a table with 6 rows, one
for July, August,....December.

Thanks.

Aug 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 23 Aug 2005 09:09:51 -0700, ma**********@hotmail.com wrote:
Is there an way to create a table of months using SQL functions? If
"July" is entered for the beginning month, and "December" is entered
for the ending month, then I'd like to create a table with 6 rows, one
for July, August,....December.

Thanks.


Whups, forgot to post the output from those two selects at the bottom.

select * from dbo.MonthList('July','December')
Select * from dbo.MonthList('December)','July'

Num MonthName
----------- ---------------
0 July
1 August
2 September
3 October
4 November
5 December

(6 row(s) affected)

Num MonthName
----------- ---------------
0 December
1 January
2 February
3 March
4 April
5 May
6 June
7 July

(8 row(s) affected)
Aug 23 '05 #2

P: n/a
On 23 Aug 2005 09:09:51 -0700, ma**********@hotmail.com wrote:
Is there an way to create a table of months using SQL functions? If
"July" is entered for the beginning month, and "December" is entered
for the ending month, then I'd like to create a table with 6 rows, one
for July, August,....December.

Thanks.


create function dbo.MonthList
( @begMonth varchar(15),
@endMonth varchar(15)
) RETURNS @MonthList TABLE
( Num int, MonthName varchar(15) )
AS
BEGIN

declare @date1 datetime
declare @date2 datetime
set @date1 = convert(datetime,@begmonth + ' 1,2000')
set @date2 = convert(datetime,@endMonth + ' 1,2000')
IF datediff(month,@date1,@date2) < 0
set @date2 = dateadd(year,1,@date2)

INSERT @MonthList (Num, MonthName)
SELECT num, datename(month,dateadd(month,num,@date1)) AS MonthName
FROM (
select 0 AS num union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9 union all
select 10 union all select 11
) AS nums
WHERE dateadd(month,num,@date1) <= @date2
return
END

GO

SELECT * from dbo.MonthList('July','December')
SELECT * from dbo.MonthList('December','July')
Aug 23 '05 #3

P: n/a
To add to Ross's comments, if you have to work with dates then you
might consider a complete calendar table:

http://www.aspfaq.com/show.asp?id=2519

Simon

Aug 24 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.