Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Get list of months, monthname and Year Expand / Collapse
Author
Message
Posted Wednesday, August 31, 2011 9:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 4, 2015 1:43 PM
Points: 186, Visits: 614
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
Post #1168185
Posted Wednesday, August 31, 2011 9:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 4, 2015 1:43 PM
Points: 186, Visits: 614
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
Post #1168195
Posted Wednesday, August 31, 2011 9:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 14,189, Visits: 37,099

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!
Post #1168206
Posted Wednesday, August 31, 2011 11:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, January 17, 2014 6:38 AM
Points: 278, Visits: 534
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:
select		t.Number as [year]
, m.[month]
, DateName(month,DateAdd(month,(m.[month]-1),0)) as MonthName
from tally t
cross join (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) m([month])
where t.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! ;)
Post #1168241
Posted Wednesday, August 31, 2011 12:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 29, 2016 12:37 AM
Points: 2,267, Visits: 5,525
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.
Post #1168269
Posted Wednesday, August 31, 2011 12:33 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 29, 2016 12:37 AM
Points: 2,267, Visits: 5,525
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())

Post #1168275
Posted Wednesday, August 31, 2011 12:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 4, 2015 1:43 PM
Points: 186, Visits: 614
Thank you for all responses...I got it!!!
Post #1168288
Posted Friday, September 2, 2011 8:19 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:44 PM
Points: 40,390, Visits: 37,598
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1169568
Posted Friday, September 2, 2011 11:18 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 9:49 PM
Points: 429, Visits: 93
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
Post #1169573
Posted Saturday, September 3, 2011 4:52 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:26 PM
Points: 4,905, Visits: 4,738
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/

Post #1169598
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse