CODEWITHSUNDEEP
sqlsql-server
user3353723
user3353723

Reputation: 219

How to use Date range in the WHERE clause

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)

Upvotes: 2

Views: 3525

Answers (5)

Ajay2707
Ajay2707

Reputation: 5798

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

Upvotes: 1

S. Miller
S. Miller

Reputation: 409

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

Upvotes: 2

shree.pat18
shree.pat18

Reputation: 21757

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)

Upvotes: 1

Daniel Cox
Daniel Cox

Reputation: 154

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

Upvotes: 2

M.Ali
M.Ali

Reputation: 69504

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

Upvotes: 1

Related Questions