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 12»»

select the last month in SQL server 2005 Expand / Collapse
Author
Message
Posted Tuesday, December 08, 2009 6:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 08, 2010 7:22 AM
Points: 46, Visits: 107
I need a help to select the last month like 2009-Nov.
Post #830616
Posted Tuesday, December 08, 2009 6:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, March 15, 2014 4:31 AM
Points: 2,808, Visits: 7,173
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())))

Post #830626
Posted Tuesday, December 08, 2009 7:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:07 AM
Points: 329, Visits: 468
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
Post #830642
Posted Tuesday, December 08, 2009 7:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 08, 2010 7:22 AM
Points: 46, Visits: 107
This should give date range from Nov01 to Nov'30
Post #830687
Posted Tuesday, December 08, 2009 7:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, March 15, 2014 4:31 AM
Points: 2,808, Visits: 7,173
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.
Post #830690
Posted Tuesday, December 08, 2009 7:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 08, 2010 7:22 AM
Points: 46, 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.
Post #830696
Posted Tuesday, December 08, 2009 7:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 08, 2010 7:22 AM
Points: 46, 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
Post #830697
Posted Tuesday, December 08, 2009 8:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, March 15, 2014 4:31 AM
Points: 2,808, Visits: 7,173
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
Post #830701
Posted Tuesday, December 08, 2009 9:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 08, 2010 7:22 AM
Points: 46, 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.
Post #830761
Posted Tuesday, December 08, 2009 11:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:05 PM
Points: 22,530, Visits: 30,307
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!



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)
Post #830858
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse