InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 16:16:17 GMT20http://www.sqlservercentral.com/Forums/Topic830616-149-1.aspxDECLARE @year VARCHAR(10)SELECT @year = '2009'SELECT LEFT(CONVERT(VARCHAR,DATEADD(MM,number,@year),112),4)+' '+ LEFT(DATENAME(MM,DATEADD(MM,number,@year)),3)FROM Master..spt_ValuesWHERE Type = 'p' AND number < DATEPART(MM,GETDATE())-1Mon, 14 Dec 2009 05:49:38 GMTMitesh Oswalhttp://www.sqlservercentral.com/Forums/Topic830616-149-1.aspxHere is the problem, everyone is shooting in the dark. If you would like the best help possible, please read and follow the instructions detailed in the first article I reference in my signature block below regarding asking for help.Without seeing your tables (or at least the relavent parts), sample data, expected results, and what you have tried so far; it is really difficult to provide good answers.Plus, if you follow the instructions in that article, you will get tested code in return. What a bonus!Tue, 08 Dec 2009 11:25:40 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic830616-149-1.aspxAgain iam getting the dec month in end of result...[2009-Nov],[2009-Oct],[2009-Sep],[2009-Aug],[2009-Jul],[2009-Jun],[2009-May],[2009-Apr],[2009-Mar],[2009-Feb],[2009-Jan],[2009-Dec]Can you please help me how to prevent this.Tue, 08 Dec 2009 09:03:22 GMTpurushotham.k9http://www.sqlservercentral.com/Forums/Topic830616-149-1.aspxThat should work,if you are looking at pivoting the data then you may find it useful to implement a calender table[url]http://codeinet.blogspot.com/2006/08/auxiliary-calendar-table-for-sql.html [/url]Tue, 08 Dec 2009 08:02:59 GMTsteveb. http://www.sqlservercentral.com/Forums/Topic830616-149-1.aspxFor this i am using following SQL: CAST(DATEPART(yyyy,getdate()) as varchar) + '-' + CONVERT(varchar(3),DATENAME(m,(DATEADD(m,-1,getdate())))).I am gussing that this should give the proper resultTue, 08 Dec 2009 07:58:23 GMTpurushotham.k9http://www.sqlservercentral.com/Forums/Topic830616-149-1.aspxI am trying to generate the months dynamically. I should have to get the months till current month(i.e Jan-Nov). In Jan 2010 i should have to generate the months from Jan-Dec.in another view i will be using them in pivot to do some calculations.Tue, 08 Dec 2009 07:56:22 GMTpurushotham.k9http://www.sqlservercentral.com/Forums/Topic830616-149-1.aspxWhat specifically do you want returned, the more specifc you can be the easier it is for people to help..do you want one row for each date in the previous month ?or a one row output with '1 Nov - 30 Nov' ?or something else.Tue, 08 Dec 2009 07:51:09 GMTsteveb. http://www.sqlservercentral.com/Forums/Topic830616-149-1.aspxThis should give date range from Nov01 to Nov'30Tue, 08 Dec 2009 07:46:03 GMTpurushotham.k9http://www.sqlservercentral.com/Forums/Topic830616-149-1.aspx[quote][b]purushotham.k9 (12/8/2009)[/b][hr]I need a help to select the last month like 2009-Nov.[/quote]Where do you want to use the result?Tue, 08 Dec 2009 07:09:15 GMTMadhivanan-208264http://www.sqlservercentral.com/Forums/Topic830616-149-1.aspxyou can use the dateadd function combined with datename[url]http://msdn.microsoft.com/en-us/library/ms186819.aspx [/url][code]SELECT DATENAME(m,(DATEADD(m,-1,GETDATE())))[/code]Tue, 08 Dec 2009 06:59:14 GMTsteveb. http://www.sqlservercentral.com/Forums/Topic830616-149-1.aspxI need a help to select the last month like 2009-Nov.Tue, 08 Dec 2009 06:49:58 GMTpurushotham.k9