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 Month Name Expand / Collapse
Author
Message
Posted Saturday, June 12, 2010 10:26 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
trishdiep (6/12/2010)
Awesome, thanks so much to Jeff and Karthik!
I want to say thank you to Jeff for helping so many of us, Much appreciated!


Thank you very much for the feedback. I appreciate it. I do have to say that I'm not alone in this, though. Lot's of good folks help others every day on this and many other forums. For that matter, look at how many people came up with good ideas or additional information on this thread alone. I'm humbled in their presence.


--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 #936445
Posted Thursday, July 1, 2010 8:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 3, 2012 3:39 AM
Points: 1, Visits: 14
ton of Thanks
Post #946570
Posted Thursday, August 30, 2012 9:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 2, 2012 8:47 PM
Points: 3, Visits: 4
see link here please http://sqltosql.blogspot.com/2012/08/sql-month-name.html
Post #1352569
Posted Thursday, August 30, 2012 9:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 2, 2012 8:47 PM
Points: 3, Visits: 4
see link here please
http://sqltosql.blogspot.com/2012/08/sql-month-name.html
Post #1352571
Posted Friday, August 31, 2012 8:25 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
yanto 93637 (8/30/2012)
see link here please
http://sqltosql.blogspot.com/2012/08/sql-month-name.html


Since it's in the article you posted a link for, let me show you that you don't need the complexity of a While Loop or Dynamic SQL.

Here's the code from the article you posted...

declare @start int,@end int
declare @qry nvarchar(100)
declare @summary nvarchar(1200)
set @start=1
set @end=13
set @summary=''
while not (@start=@end)
begin
if @start=@end-1
set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber, DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName')qry)
else
set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber, DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName
union ')qry)

set @summary=@summary+@qry
set @start=@start+1
end
exec (@summary)


The following snippet does the same thing without the loop or the dynamic SQL.

WITH
cteTally AS
(
SELECT TOP 12
N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns
)
SELECT MonthNumber = N,
MonthName = DATENAME(mm,DATEADD(mm,N,-1))
FROM cteTally
;

Of course, since this is an SQL Server 2000 forum, the code above won't work in SQL Server 2000. The following will, though... and, since it uses a Tally Table, the code becomes even more simple and works in all versions of SQL Server
.
 SELECT MonthNumber = N,
MonthName = DATENAME(mm,DATEADD(mm,N,-1))
FROM dbo.Tally
WHERE N <= 12
;

For more information on what a Tally Table (or cteTally) is and how it can be used to replace certain loops in a very high performance fashion, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/62867/


--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 #1352849
Posted Sunday, September 2, 2012 8:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 2, 2012 8:47 PM
Points: 3, Visits: 4
Jeff ... Thanks a lot for your explanation.
Post #1353310
Posted Monday, September 3, 2012 2:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 4:10 PM
Points: 16, Visits: 65
Many thanks for all the answers here particularly the ones from Jeff and Karthik. They enable me to get rid of a lot of CASE statements!
Post #1353383
Posted Monday, September 3, 2012 10:22 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
Thanks for the feedback, folks.

--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 #1353594
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse