Regarding SQL Server programming 1

[anilrajanala]

How to find just for last month data from a table.
i.e current months is dec,I need rows for Nov.

Thanks in advance

 

[SQLSister]

Select Col1, col2
from table1
where month(datefield) = month(getdate())-1

This assumes your field is a datetime data type.

 

[anilrajanala]

HI

But this returns for all years.i need only just for last month.i.e if current date is dec2003 I need for just Nov,2003.

Your query returns for ....nov 2001,Nov 2002,nov 2003

Thanks,
Anil

 

[ecobb]

Try this:

Select Col1, col2
from table1
where month(datefield) = month(getdate())-1
AND year(datefield) = year(getdate())

This should work great until you get to next month (Jan 2004) and it looks for December 2004. You'll need to put some IF statements in there to contend with that, but all other months should work fine.

Hope This Helps!

Ecobb
"Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer." - Homer Simpson

 

[SQLSister]

Select Col1, col2
from table1
where month(datefield) = month(getdate())-1
AND year(datefield) = year(getdate()-32)
That should account for the Jan dec thing.

 

[BuGlen]

Another way would be to calculate the start and end dates for the previous month using the DATEADD function and use the between operator in the query:

-----
DECLARE @dtStart datetime, @dtEnd datetime, @vcMonth varchar(2), @vcYear varchar(4)

---- Get the start date
SET @vcMonth = Month(getdate()) - 1
SET @vcYear = Year(getdate())
SET @dtStart = DATEADD(Month, -1, CAST(@vcMonth + '-1-' + @vcYear as datetime))

---- Get the end date

SET @vcMonth = month(getdate())
SET @dtEnd = DATEADD(Day, -1, CAST(@vcMonth + '-1-' + @vcYear as datetime))

---- Select your records
SELECT col1, col2, col3
FROM table1
WHERE datefield between @dtStart and @dtEnd
-----

Hope this helps.

Glen Appleton

VB.Net student.

 

[BuGlen]

Oops! Correction: The previous code would return the previous 2 months of data. Here is the corrected code:

-----

DECLARE @dtStart datetime, @dtEnd datetime, @vcMonth varchar(2), @vcYear varchar(4)

SET @vcMonth = Month(getdate())
SET @vcYear = Year(getdate())

---- Get the start date
SET @dtStart = DATEADD(Month, -1, CAST(@vcMonth + '-1-' + @vcYear as datetime))

---- Get the end date
SET @dtEnd = DATEADD(Day, -1, CAST(@vcMonth + '-1-' + @vcYear as datetime))

---- Select your records
SELECT col1, col2, col3
FROM table1
WHERE datefield between @dtStart and @dtEnd

-----

Glen Appleton

VB.Net student.

 

[ecobb]

"AND year(datefield) = year(getdate()-32) " Cool! It has never occured to me to use -32 in cases like this SQLSister! Thanks! You get a star!

Hope This Helps!

Ecobb
"Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer." - Homer Simpson

 

[TheBugSlayer]

SQLSister,
in your code

Select Col1, col2
from table1
where month(datefield) = month(getdate())-1
AND year(datefield) = year(getdate()-32)

what happens if datefield = 12/28/2003 and now = 01/12/2004 for instance? Will 12 = 1-1 => False AND 2003 = 2003 => True return any match?

 

[SQLSister]

Good catch! You are right, you will have to account for the jan dec problem in month as a date of 1/1/2004 will return 0 for the month when 1 is subtracted. I guess you will need a case statement here to change a 0 result to a 12. NO time to write one right now, hopefully someone else will throw it into the mix.

 

[Golom]

Or use DateAdd

Select Col1, col2
from table1
where month(datefield) = month(DateAdd("m",-1,getdate())
AND year(datefield) = year(DateAdd("m",-1,getdate())