Get list of months, monthname and Year

Question

Post reply

Get list of months, monthname and Year

komal145

SSCrazy Eights

Points: 9915
More actions
August 31, 2011 at 9:25 am

#242630

Hi,
I want to create a temp table and insert all the list of
monthorder , Monthnames , year
1 january 2011
2 february 2011
.
.
.
12 December 2011
Thanks,
Komal

Viewing 15 posts - 1 through 15 (of 29 total)

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

komal145 SSCrazy Eights Points: 9915 More actions · Answer 1

I got query to get month order and year but not getting month names.

SELECT [year], [month]

FROM (

SELECT 2005 AS [year] UNION ALL

SELECT 2006 UNION ALL

SELECT 2007 UNION ALL

SELECT 2008 UNION ALL

SELECT 2009 UNION ALL

SELECT 2010 UNION All

SELECT 2011 UNION All

SELECT 2012 UNION ALL

SELECT 2013

) years

CROSS JOIN (

SELECT 1 AS [month] 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 UNION ALL

SELECT 12

) monthsorder

ORDER BY years.[year], monthsorder.[month]

can anybody help getting monthnames in same query??? so that i can create table

Lowell SSC Guru Points: 323518 More actions · Answer 2

SELECT [year], [month], datename(month,dateadd(mm,[month] -1,DATEADD(yy, DATEDIFF(yy,0,[year] - 1900), 0)))

FROM (

SELECT 2005 AS [year] UNION ALL

SELECT 2006 UNION ALL

SELECT 2007 UNION ALL

SELECT 2008 UNION ALL

SELECT 2009 UNION ALL

SELECT 2010 UNION All

SELECT 2011 UNION All

SELECT 2012 UNION ALL

SELECT 2013

) years

CROSS JOIN (

SELECT 1 AS [month] 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 UNION ALL

SELECT 12

) monthsorder

ORDER BY years.[year], monthsorder.[month]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

paul_ramster Ten Centuries Points: 1382 More actions · Answer 3

In just a couple of years, this table is only going to contain past dates.

If you're going to create a calendar table, it should contain more years than this, I would have thought - or be dynamically created as and when required spanning enough years before and after the current date.

Also, Lowell's suggestion is needlessly complicated - the months have the same name every year, so there is no need to provide a variable year in the MonthName calculation:

selectt.Number as [year]

,m.[month]

,DateName(month,DateAdd(month,(m.[month]-1),0)) as MonthName

fromtally t

cross join(values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) m([month])

wheret.Number between DatePart(year,GetDate()) - 100 and DatePart(year,GetDate()) + 200

Of course, this assumes that you have a Tally table handy, but if you haven't, then you should! 😉

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

If you dont have a tally table, you can utilize this code :

DECLARE @StartDATE DATETIME

SET @StartDATE = '01-01-1985'

;WITH Tens (N) AS

(

SELECT 0 N UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0

),

Thousands (N) AS

(

SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3

),

Tally AS

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands

),

Month_Numbers ( MonthNum , Month_Name) AS

(

SELECT 1 , 'January'

UNION ALL SELECT 2 , 'February'

UNION ALL SELECT 3 , 'March'

UNION ALL SELECT 4 , 'April'

UNION ALL SELECT 5 , 'May'

UNION ALL SELECT 6 , 'June'

UNION ALL SELECT 7 , 'July'

UNION ALL SELECT 8 , 'August'

UNION ALL SELECT 9 , 'September'

UNION ALL SELECT 10 , 'October'

UNION ALL SELECT 11 , 'November'

UNION ALL SELECT 12 , 'December'

)

SELECT Nums.MonthNum , DATEPART(YY,DATEADD(M,N-1,@StartDATE)) [YEAR], DATENAME(MM,DATEADD(M,N-1,@StartDATE)) [MONTH]

FROM Tally T

JOIN Month_Numbers Nums

ON DATENAME(MM,DATEADD(M,N-1,@StartDATE)) = Nums.Month_Name

WHERE DATEPART(YY,DATEADD(M,N-1,@StartDATE)) <= YEAR(GETDATE())

The @StartDate will specify from what year your output should start.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

A more cleaner version:

DECLARE @StartDATE DATETIME

SET @StartDATE = '01-01-1985'

;WITH Tens (N) AS

(

SELECT 0 N UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0

),

Thousands (N) AS

(

SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3

),

Tally AS

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands

)

SELECT MonthNum =

CASE T.N % 12

WHEN 0 THEN 12

ELSE T.N % 12

END

, DATEPART(YY,DATEADD(M,N-1,@StartDATE)) [YEAR]

, DATENAME(MM,DATEADD(M,N-1,@StartDATE)) [MONTH]

FROM Tally T

WHERE DATEPART(YY,DATEADD(M,N-1,@StartDATE)) <= YEAR(GETDATE())

komal145 SSCrazy Eights Points: 9915 More actions · Answer 6

komal145

SSCrazy Eights

Points: 9915

August 31, 2011 at 12:49 pm

#1375639

Thank you for all responses...I got it!!!

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

komal145 (8/31/2011)
Thank you for all responses...I got it!!!

Cool! Please post the code you ended up using so that we all may learn. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tamil.selvanmca Ten Centuries Points: 1367 More actions · Answer 8

WITH DateYear AS

(

SELECT 0 AS num

UNION ALL

SELECT num + 1 FROM DateYear

WHERE num < 11

)

SELECT CONVERT(DATE,DATEADD(MONTH,num,'2011')) AS Date from DateYear

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Very cool code @ColdCoffee.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 10

Welsh Corgi (9/3/2011)
Very cool code @ColdCoffee.

Thank you Welsh!

R.P.Rozema SSChampion Points: 12317 More actions · Answer 11

For even cleaner code, your method needs a 0-based tally table. This is easily accomplished by adding -1 to row_number(). Your code then looks like this:

DECLARE @StartDATE DATETIME;

SET @StartDATE = {d '1985-01-01'};

;WITH Tens (N) AS

(

SELECT 0 N UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0

),

Thousands (N) AS

(

SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3

),

Tally AS

(

SELECT -1 + ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands

)

SELECT MonthNum = T.N

, DATEPART(YY,DATEADD(M,T.N,@StartDATE)) [YEAR]

, DATENAME(MM,DATEADD(M,T.N,@StartDATE)) [MONTH]

FROM Tally T

WHERE DATEPART(YY,DATEADD(M,T.N,@StartDATE)) <= YEAR(GETDATE());

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 12

R.P.Rozema (9/4/2011)
This is easily accomplished by adding -1 to row_number().

Thanks Rozema for "bettering" it out. If zero based tally table will make it cleaner, we can do something like

Tally AS

(

SELECT 0

UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands

)

This way, we can stop the Compute Scalar operator and could the engine from calculating for all the rows. Right, R.P.R ?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 13

And coming to my code, a 0-based will throw the CASE statement out of sync. That's why i din't use it 🙂

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 14

Added more details in code and optimized it.

DECLARE @StartDATE DATETIME

,@EndDate DATETIME

,@YearofEndDate INT

SELECT @StartDATE = '01-01-1985' , @EndDate = GETDATE()

SET @YearofEndDate = YEAR(@EndDate)

;WITH Tens (N) AS

(

SELECT 0 N UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0

),

Thousands (N) AS

(

SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3

),

Numbers AS

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands

)

SELECT MonthNum =

CASE T.N % 12

WHEN 0 THEN 12

ELSE T.N % 12

END

, DATEPART(YY,DATEADD(M,N-1,@StartDATE)) [Year]

, DATENAME(MM,DATEADD(M,N-1,@StartDATE)) [Month]

, DATEADD(M,N-1,@StartDATE) [StartDateOfMonth]

, DATEADD (DAY , -1, DATEADD(MONTH , 1 , DATEADD(M,N-1,@StartDATE)) ) EndDateOfMonth

FROM Numbers T

WHERE DATEPART(YY,DATEADD(M,N-1,@StartDATE)) <= @YearofEndDate

ORDER BY [Year] , MonthNum