2

I want to modify my Where clause in my SQL Server Query below so that it would select ALL records from the previous month.

Example: if I run the query on 20 Feb, it should extract data for 1 Jan to 31 Jan

I have tried using the following but as you may notice, it picks up the records a month back from the day of execution.

WHERE date_col >= cast(dateadd(Month, -1, getdate()) as date) and
      date_col <= cast(getdate() as date)
user3353723
  • 221
  • 1
  • 7
  • 15
  • I don't have a local install of SQL to bang this out against, but basically you have to work out what the first day of the current month is, then get the records that fall between -1 month of that date and that date. `dateadd(day, -1 * datepart(day, getdate()), getdate())` or something like that. – GalacticCowboy Jun 12 '14 at 01:51

5 Answers5

2

I'm not claiming this is the best way, but it should work:

SELECT * from YourTable
WHERE  DATEPART(Month, date_col) = (DATEPART(Month,GETDATE()) - 1)
AND    DATEPART(Year, date_col) = DATEPART(Year,DATEADD(Month, -1, GETDATE()))
Daniel Cox
  • 154
  • 1
  • 9
  • This is the easiest way of doing this but not the best as this `DATEPART` function on column will make this query Non-Sargble . – M.Ali Jun 12 '14 at 01:59
  • You're right. That will definitely have an effect, however it will include all of the data. – Daniel Cox Jun 12 '14 at 03:29
2

Sql Server 2012 and Later

EOMONTH looks like it may be a useful function in this case. EOMONTH(getdate(), -1) is the end of last month. EOMONTH(getdate(), -2) is the end of the month before.

Try something like 

WHERE date_col >= cast(EOMONTH(getdate(), -1) as date) and date_col <=
cast(EOMONTH(getdate(),-2) as date);
M.Ali
  • 67,945
  • 13
  • 101
  • 127
S. Miller
  • 379
  • 3
  • 15
  • 1
    `EOMONTH` only works in newer versions of SQL Server, but it is the right way to do this now. – mattmc3 Jun 12 '14 at 01:59
1

TO get the last and first day of previous month :

SELECT DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH,-1,GETDATE())), 0) AS First_Day_Of_Last_Month
       ,DATEADD(s,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()),0))       AS Last_day_Of_Last_Month

Result:

╔═════════════════════════╦═════════════════════════╗
║ First_Day_Of_Last_Month ║ Last_day_Of_Last_Month  ║
╠═════════════════════════╬═════════════════════════╣
║ 2014-05-01 00:00:00.000 ║ 2014-05-31 23:59:59.000 ║
╚═════════════════════════╩═════════════════════════╝

Your Query

WHERE date_col >= DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH,-1,GETDATE())), 0)
  AND date_col <= DATEADD(s,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()),0))
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • Not to be nit-picky, but my answer is more accurate. Your answer leaves off a second out of the possible date frame. If anything, I'd highly recommend that you change your DATEADD(s, -1...) to subtract a single nanosecond DATEADD(ns, -1...). This may seem insignificant, but not when you can't fully reconcile the data by hand and get the same answer. – Daniel Cox Jun 12 '14 at 03:26
1

Since you want all records from the previous month, you could just compare the month and year parts of the current date and the date_col values, like so:

select * 
from yourtable
where 
(month(date_col) = month(getdate()) - 1
and year(date_col) = year(getdate()) 
and month(getdate()) <> 1)
or
(month(date_col) = 12
 and year(date_col) = year(getdate()) - 1
 and month(getdate()) = 1)
shree.pat18
  • 21,449
  • 3
  • 43
  • 63
1

This query also work as already ask by some one and good rating of answer too.

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

Get the records of last month in SQL server

Community
  • 1
  • 1
Ajay2707
  • 5,690
  • 6
  • 40
  • 58