Get all months from Jan to Dec

Question

Post reply

Get all months from Jan to Dec

kalaivanireddy

SSC Veteran

Points: 261
More actions
December 27, 2010 at 3:32 am

#229891

Hi friends,
I want to fill a dropdownlist in asp.net 2.0 with all months from january to December.I want to get the months name in a stored procedure and do my later process.
Plz help me how to auto generate months
thanks in advance,
kalai

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This should do the trick:

WITH MonthNumbers ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])

AS (

SELECT 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12

)

SELECT MonthNumber, MonthName = DATENAME(month, DATEADD(month, MonthNumber - 1,0))

FROM (

SELECT *

FROM MonthNumbers MN

) AS src

UNPIVOT (M FOR MonthNumber IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS upvt

-- Gianluca Sartori

WayneS SSC Guru Points: 95460 More actions · Answer 2

Here's a method that utilizes a small, recursive CTE:

;WITH Months (N, MyDate) AS

(

-- get the first day of the current year

-- N is the MonthNumber

SELECT 1, DateAdd(year, DateDiff(year, 0, GetDate()), 0)

UNION ALL

-- add one month recursively until we have 12 months

SELECT N+1, DateAdd(month, 1, MyDate)

FROM Months

WHERE N < 12

)

-- get the names of the months, order by month number

SELECT MonthName = DateName(month, MyDate)

FROM Months

ORDER BY N;

Edit: However, in comparing this solution to Gianluca's, his produces a tighter execution plan that consistently runs slightly faster.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 3

Maybe it's just me, but isn't this a solution in search of a problem? How often do the names of the months change? Just code the names in... (or am I missing something)

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

jcrawf02 (12/28/2010)
Maybe it's just me, but isn't this a solution in search of a problem? How often do the names of the months change? Just code the names in... (or am I missing something)

True, but... do you speak Italian, Jon?

Do you want to know the month names in Italian?

Run the above code after issuing "SET Language Italian" and see. 🙂

-- Gianluca Sartori

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 5

Gianluca Sartori (12/28/2010)
jcrawf02 (12/28/2010)
Maybe it's just me, but isn't this a solution in search of a problem? How often do the names of the months change? Just code the names in... (or am I missing something)
True, but... do you speak Italian, Jon?
Do you want to know the month names in Italian?
Run the above code after issuing "SET Language Italian" and see. 🙂

Ah, good point. Stupid Americans. 😀

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

You could also use this:

with MonthNumbers as (

select 1 as MonthNum union all

select 2 as MonthNum union all

select 3 as MonthNum union all

select 4 as MonthNum union all

select 5 as MonthNum union all

select 6 as MonthNum union all

select 7 as MonthNum union all

select 8 as MonthNum union all

select 9 as MonthNum union all

select 10 as MonthNum union all

select 11 as MonthNum union all

select 12 as MonthNum)

select

MonthNum,

DATENAME(MONTH, DATEADD(mm, MonthNum - 1, cast('19000101' as datetime)))

from

MonthNumbers;

If you want to guarantee the order, do the sort in memory on the client using the MonthNum.

They are about 6 one-half dozen the other with regard to speed. They have totally different execution plans, however.

nigel. SSChampion Points: 11636 More actions · Answer 7

If you're doing this in ASP.NET why go to the database at all, why not do it in the page itself?

That would be the more usual way of doing this, especially if you wanted to display the month names in the user's own language, rather than whatever language the database is using (which might well differ).

Nigel
How to post questions

nigel. SSChampion Points: 11636 More actions · Answer 8

Try this: http://msdn.microsoft.com/en-us/library/system.globalization.datetimeformatinfo.monthnames.aspx

Nigel
How to post questions