Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select the last month in SQL server 2005


select the last month in SQL server 2005

Author
Message
purushotham.k9
purushotham.k9
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 107
I need a help to select the last month like 2009-Nov.
steveb.
steveb.
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3148 Visits: 7195
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
Madhivanan-208264
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 476
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
purushotham.k9
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 107
This should give date range from Nov01 to Nov'30
steveb.
steveb.
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3148 Visits: 7195
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
purushotham.k9
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 107
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
purushotham.k9
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 107
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.
steveb.
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3148 Visits: 7195
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
purushotham.k9
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 107
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
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26578 Visits: 38132
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!

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search