CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
jonhobbs
jonhobbs

Reputation: 27952

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

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?

Upvotes: 4

Views: 19475

Answers (4)

Maz
Maz

Reputation: 11

Simple just use what I just used: DATEDIFF(mm,dateFinished,GETDATE()) = 1

SELECT * 
FROM items 
WHERE DATEDIFF(mm,dateFinished,GETDATE()) = 1

Upvotes: 1

Andomar
Andomar

Reputation: 238048

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.

Upvotes: 7

user264855
user264855

Reputation:

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

Upvotes: 0

Matt Whitfield
Matt Whitfield

Reputation: 6574

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

Upvotes: 8

Related Questions