InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 06:31:49 GMT20http://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxThanks for the feedback, folks.Mon, 03 Sep 2012 10:22:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxMany 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! :-)Mon, 03 Sep 2012 02:16:01 GMTFear Naughthttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxJeff ... Thanks a lot for your explanation.Sun, 02 Sep 2012 20:20:49 GMTyanto 93637http://www.sqlservercentral.com/Forums/Topic453088-169-1.aspx[quote][b]yanto 93637 (8/30/2012)[/b][hr]see link here please[url=http://sqltosql.blogspot.com/2012/08/sql-month-name.html]http://sqltosql.blogspot.com/2012/08/sql-month-name.html[/url][/quote]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...[code="sql"]declare @start int,@end intdeclare @qry nvarchar(100)declare @summary nvarchar(1200)set @start=1set @end=13set @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)[/code]The following snippet does the same thing without the loop or the dynamic SQL.[code="sql"]WITHcteTally 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;[/code]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.[code="sql"] SELECT MonthNumber = N, MonthName = DATENAME(mm,DATEADD(mm,N,-1)) FROM dbo.Tally WHERE N <= 12;[/code]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.[url]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]Fri, 31 Aug 2012 08:25:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxsee link here please[url=http://sqltosql.blogspot.com/2012/08/sql-month-name.html]http://sqltosql.blogspot.com/2012/08/sql-month-name.html[/url]Thu, 30 Aug 2012 21:29:15 GMTyanto 93637http://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxsee link here please http://sqltosql.blogspot.com/2012/08/sql-month-name.htmlThu, 30 Aug 2012 21:27:39 GMTyanto 93637http://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxton of ThanksThu, 01 Jul 2010 20:56:39 GMTvishal.janihttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspx[quote][b]trishdiep (6/12/2010)[/b][hr]Awesome, thanks so much to Jeff and Karthik!I want to say thank you to Jeff for helping so many of us, Much appreciated!:-)[/quote]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. :-)Sat, 12 Jun 2010 10:26:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxAwesome, thanks so much to Jeff and Karthik!I want to say thank you to Jeff for helping so many of us, Much appreciated!:-)Sat, 12 Jun 2010 09:07:28 GMTtrishdiephttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspx[quote][b]Venkatesan Prabu (6/6/2010)[/b][hr]drop table sample_tablecreate table sample_table (id int, dat datetime)insert into sample_table values(1,getdate())select * from sample_tableselect id,dat from sample_table where datename(month,dat)='June'----------------------------------------- id dat----------------------------------------- 1 2010-06-07 10:54:52.607-----------------------------------------Cheers,Venkatesan Prabu .Jhttp://venkattechnicalblog.blogspot.com/[/quote]Ummm... I don't get it. How does that deal with the original request of converting month numbers to month names?Sun, 06 Jun 2010 21:28:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxdrop table sample_tablecreate table sample_table (id int, dat datetime)insert into sample_table values(1,getdate())select * from sample_tableselect id,dat from sample_table where datename(month,dat)='June'----------------------------------------- id dat----------------------------------------- 1 2010-06-07 10:54:52.607-----------------------------------------Cheers,Venkatesan Prabu .Jhttp://venkattechnicalblog.blogspot.com/Sun, 06 Jun 2010 18:58:47 GMTVenkatesan Prabuhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxSimilarly if you want to list all the month names for a year using a T-SQL statement, you can do this:SELECT Number + 1 as [MonthNumber],DateName(mm,DATEADD(mm,Number,0)) as [MonthName]FROM master..spt_valuesWHERE Type = 'P' and Number < 12Fri, 21 May 2010 00:55:37 GMTmandarkavishwarhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxGailyou rock!but you knew that alreadythis also works in SQL Server 2000Tue, 20 Apr 2010 13:41:18 GMTSeggerman-675349http://www.sqlservercentral.com/Forums/Topic453088-169-1.aspx[Edit] Ack, thought I had scrolled down all the way, apparently I was wrong.Since I've posted anyways, might as well mention that doing it this way automatically adjusts for language settings.[code="sql"]set language spanishselect DATENAME(m,DATEADD(m,1,-1))--Eneroselect DATENAME(m,DATEADD(m,2,-1))--Febreroselect DATENAME(m,DATEADD(m,3,-1))--Marzoselect DATENAME(m,DATEADD(m,4,-1))--Abril[/code]Wed, 07 Apr 2010 22:49:10 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxThe character conversion will slow things down quite a bit if you happen to use the for batch programming on millions of rows.Fri, 02 Apr 2010 18:11:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxHere is one line solution in my implementation.[url=http://praveenbattula.blogspot.com/2009/04/how-to-know-month-name-from-month-index.html]http://praveenbattula.blogspot.com/2009/04/how-to-know-month-name-from-month-index.html[/url]Mon, 29 Mar 2010 02:43:09 GMTbattula.praveenhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxSuper Logic..Keep Rocking:)Mon, 18 Jan 2010 02:36:12 GMTrajesh.vellorehttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspx[quote][b]karthikeyan (3/12/2009)[/b][hr]A little modification in Jeff's code.SELECT DateName(mm,DATEADD(mm,MonthNumber,-1))[/quote]Yep... good call.Thu, 12 Mar 2009 18:55:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxA little modification in Jeff's code.SELECT DateName(mm,DATEADD(mm,MonthNumber,-1))Thu, 12 Mar 2009 03:30:12 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxYou bet... thanks for the feedback.Wed, 11 Mar 2009 21:33:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxthanks a ton.....it helped. :DWed, 11 Mar 2009 06:50:49 GMTvikrammsteinhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspx[quote][b]GilaMonster (2/8/2008)[/b][hr]Not directly, though you could convert the month number inot a date.SELECT DATENAME(month,'1900/' + CAST(monthNumber AS VARCHAR(2)) + '/01')[/quote]Heh, too much VARCHAR in that for me, Gail... :PSELECT DATENAME(mm,DATEADD(mm, MonthNumber-1,0))Tue, 11 Mar 2008 09:15:57 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxthank you GilaMonster...Tue, 11 Mar 2008 08:50:27 GMTraju.msoftprofhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspx[b]Thanks very much[/b]Fri, 08 Feb 2008 22:36:23 GMTShakti Singh Dulawathttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxNot directly, though you could convert the month number inot a date.SELECT DATENAME(month,'1900/' + CAST(monthNumber AS VARCHAR(2)) + '/01')Fri, 08 Feb 2008 01:52:47 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxThanksThats goodBut is there is way so we can get month name using month number.Fri, 08 Feb 2008 01:45:42 GMTShakti Singh Dulawathttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxSQL has a DateName function, however it takes a datetime as a parameter, not a numberSELECT DATENAME(month, GETDATE()) AS 'Month Name'returnsMonth Name ------------------------------ FebruaryFri, 08 Feb 2008 01:30:28 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic453088-169-1.aspxHi all Is there any best way to get month name in SQL SERVER 2005.I know that we can do this using our own function, but just i want to conform that if SQL SERVER provide any function for month name if we have month number i.e. 1,2..12ThanksFri, 08 Feb 2008 00:23:40 GMTShakti Singh Dulawat