Get the records of last month in SQL server

Question

I want to get the records of last month based on my db table [member] field "date_created".

What's the sql to do this?

For clarification, last month - 1/8/2009 to 31/8/2009

If today is 3/1/2010, I'll need to get the records of 1/12/2009 to 31/12/2009.

Answer 1

One way to do it is using the DATEPART function:

select field1, field2, fieldN from TABLE where DATEPART(month, date_created) = 4 
and DATEPART(year, date_created) = 2009

will return all dates in april. For last month (ie, previous to current month) you can use GETDATE and DATEADD as well:

select field1, field2, fieldN from TABLE where DATEPART(month, date_created) 
= (DATEPART(month, GETDATE()) - 1) and 
DATEPART(year, date_created) = DATEPART(year, DATEADD(m, -1, GETDATE()))

Answer 2

SELECT * 
FROM Member
WHERE DATEPART(m, date_created) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(y, date_created) = DATEPART(y, DATEADD(m, -1, getdate()))

You need to check the month and year.

Answer 3

select * from [member] where DatePart("m", date_created) = DatePart("m", DateAdd("m", -1, getdate())) AND DatePart("yyyy", date_created) = DatePart("yyyy", DateAdd("m", -1, getdate()))

Answer 4

Add the options which have been provided so far won't use your indexes at all.

Something like this will do the trick, and make use of any index on the table.

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = dateadd(mm, -1, getdate())
SET @StartDate = dateadd(dd, datepart(dd, getdate())*-1, @StartDate)
SET @EndDate = dateadd(mm, 1, @StartDate)

SELECT *
FROM Member
WHERE date_created BETWEEN @StartDate AND @EndDate