3

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

user3190075
  • 69
  • 2
  • 2
  • 11
  • 1
    so many bad answers here, currently no useful answers, check the link and don't use the selected answer from the link. – t-clausen.dk Jan 13 '14 at 14:30

5 Answers5

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()
Obsidian Phoenix
  • 4,083
  • 1
  • 22
  • 60
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.

M.Ali
  • 67,945
  • 13
  • 101
  • 127
1
select * from table
where month(date)=month(getdate())-1
demongolem
  • 9,474
  • 36
  • 90
  • 105
Cav
  • 11
  • 1
1

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?

Agna JirKon Rx
  • 2,321
  • 2
  • 29
  • 44
-1

You can try to use the between statement to get the specific dates:

Select * from table where date between '01-01-2014' and '14-01-2014'
XcisioN
  • 107
  • 9