SQL server - Select all items with a date on the previous month

Question

Hi Guys,

I have a table in my SQL Server database called "items" which has a column called "dateFinished".

I have a script that will run on the 1st day of each month which needs to select all items that finished in the previous month.

So, for example, on the 1st February it will need to select all items where the dateFinished is greater than or equal to 00:00 on the 1st of January and less than 00:00 on 1st February.

it also needs to work across new years (e.g. DEC - JAN).

Any ideas?

Answer 1

Select * 
  from items 
 where datefinished >= dateadd(m, datediff(m, 0, GETDATE()) - 1, 0) 
   AND datefinished < dateadd(m, datediff(m, 0, GETDATE()), 0)

Answer 2

I would start by checking out the DATEADD function http://msdn.microsoft.com/en-us/library/ms186819.aspx

Answer 3

You could get a day of the previous month with dateadd(m,-1,getdate()). Then, filter on the year and month of that date in a where clause, like:

select *
from items
where datepart(yy,dateFinished) = datepart(yy,dateadd(m,-1,getdate()))
and datepart(m,dateFinished) = datepart(m,dateadd(m,-1,getdate()))

This should work across years, and also if the query is run on a later day than the first of the month.