Select all where date in Last month sql

Question

How can I get last month date like

select * from table where date in ( last month  )

I dont want the last 30 days AND how can I get last month automatically

Solution 1

Edit

if you mean last month from today. or previous month from a specific date then you need to do something like this

SELECT DATEPART(MONTH, DATEADD(MONTH, -1, [Date]))

Or to get records from previous month of the year you can do something like this

SELECT * FROM Table
WHERE  MONTH(Date) =  DATEPART(MONTH, DATEADD(MONTH, -1, [Date]))
AND YEAR(Date) =    DATEPART(YEAR, DATEADD(MONTH, -1, [Date]))    --<-- or pass year for which year you are checking 

To make your aquery SARGable (Suggested by t-clausen.dk)

select * from table 
where date >=dateadd(m, datediff(m, 0, current_timestamp)-1, 0) 
and date < dateadd(m, datediff(m, 0, current_timestamp)-1, 0)

Read here more about sargable Queries when working with date/datetime datatypes.

Solution 2

Assuming you want all items where the date is within the last month i.e. between today and 30/31 days ago:

Select *
From Table
Where Date Between DATEADD(m, -1, GETDATE()) and GETDATE()

Solution 3

select * from table
where month(date)=month(getdate())-1

Solution 4

You can use this

Select * from table where date between @startdate and @enddate

Or

SELECT * FROM DATE_SAMPLE WHERE 
DATEPART(YEAR, SAMPLE_DATE) = '2013' AND 
DATEPART(MONTH,SAMPLE_DATE) = '01' AND 
DATEPART(DAY, SAMPLE_DATE) = '01'

Is it usefull for you?

View more solutions