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

In Sql server for last one month:

select * from tablename 
where order_date > DateAdd(WEEK, -1, GETDATE()+1) and order_date<=GETDATE()

Answer 2

If you are looking for last month so try this,

SELECT
FROM  #emp 
WHERE DATEDIFF(MONTH,CREATEDDATE,GETDATE()) = 1

If you are looking for last month so try this,

SELECT
FROM #emp
WHERE DATEDIFF(day,CREATEDDATE,GETDATE()) between 1 and 30

Answer 3

A simple query which works for me is:

select * from table where DATEADD(month, 1,DATEFIELD) >= getdate()

Answer 4

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
SET @EndDate = DATEADD(mm, 1, @StartDate)

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

An upgrade to mrdenny's solution, this way you get exactly last month from YYYY-MM-01

Answer 5

DECLARE @curDate INT = datepart( Month,GETDATE())
IF (@curDate = 1)
    BEGIN
        select * from Featured_Deal
        where datepart( Month,Created_Date)=12 AND datepart(Year,Created_Date) = (datepart(Year,GETDATE())-1)

    END
ELSE
    BEGIN
        select * from Featured_Deal
        where datepart( Month,Created_Date)=(datepart( Month,GETDATE())-1) AND datepart(Year,Created_Date) = datepart(Year,GETDATE())

    END 

Answer 6

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()))

You need to check the month and year.