select the last month in SQL server 2005

Question

Post reply

select the last month in SQL server 2005

purushotham.k9

SSCommitted

Points: 1992
More actions
December 8, 2009 at 6:49 am

#135805

I need a help to select the last month like 2009-Nov.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you can use the dateadd function combined with datename

http://msdn.microsoft.com/en-us/library/ms186819.aspx

SELECT DATENAME(m,(DATEADD(m,-1,GETDATE())))

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

purushotham.k9 (12/8/2009)
I need a help to select the last month like 2009-Nov.

Where do you want to use the result?

Madhivanan

Failing to plan is Planning to fail

purushotham.k9 SSCommitted Points: 1992 More actions · Answer 3

purushotham.k9

SSCommitted

Points: 1992

December 8, 2009 at 7:46 am

#1089236

This should give date range from Nov01 to Nov'30

steveb. SSC-Forever Points: 46733 More actions · Answer 4

What 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.

purushotham.k9 SSCommitted Points: 1992 More actions · Answer 5

I 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.

purushotham.k9 SSCommitted Points: 1992 More actions · Answer 6

For 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 result

steveb. SSC-Forever Points: 46733 More actions · Answer 7

That should work,

if you are looking at pivoting the data then you may find it useful to implement a calender table

http://codeinet.blogspot.com/2006/08/auxiliary-calendar-table-for-sql.html

purushotham.k9 SSCommitted Points: 1992 More actions · Answer 8

Again 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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Here 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!

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 10

DECLARE @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_Values

WHERE

Type='p' AND

number < DATEPART(MM,GETDATE())-1

Regards,
Mitesh OSwal
+918698619998