Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Tuesday, September 17, 2013 2:07 PM
Points: 171, Visits: 556
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: Tuesday, September 17, 2013 2:07 PM
Points: 171, Visits: 556
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 @ 7:44 AM
Points: 12,910, Visits: 32,015

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
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: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
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: Tuesday, September 17, 2013 2:07 PM
Points: 171, Visits: 556
Thank you for all responses...I got it!!!
Post #1168288
Posted Friday, September 2, 2011 8:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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: Sunday, June 22, 2014 9:45 PM
Points: 422, Visits: 92
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: Today @ 7:06 AM
Points: 4,196, Visits: 4,268
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