How to retrieve records that are from two months from the current date

Question

So what I am trying to do is when I run the query, I want to return all records that were in the month two months from the current month. For example, lets say the current month is November, when the query runs, I want returned all records from September and only September. If I run the query in lets say October, I want all records from August and only August. I am trying to do this in MS SQL. Thanks for any advice.

What have you tried so far? Why didn't it work? What's wrong with a `WHERE` that makes use of the `>`/`>=` and `<`/`<=` operators? — Thom A, Nov 19 '20 at 15:00
Welcome to stackoverflow - please review the [guidelines on how to ask questions](https://stackoverflow.com/help/how-to-ask) - this will help you to get useful answers to your question — Matthew Hegarty, Nov 19 '20 at 15:16
https://stackoverflow.com/questions/5425627/sql-query-for-todays-date-minus-two-months — , Aug 31 '22 at 05:09

score 0 · Answer 1 · answered Nov 19 '20 at 15:01

In SQL Server, you can use:

where datecol >= dateadd(month, -3, datefromparts(year(getdate()), month(getdate()), 1)) and
      datecol < dateadd(month, -2, datefromparts(year(getdate()), month(getdate()), 1))

This is index- and optimizer- friendly. If you don't care about performance, you can use datediff():

where datediff(month, datecol, getdate()) = 2

score 0 · Answer 2 · answered Nov 19 '20 at 15:01

0

This can be done in a nice 1 liner.

WHERE NOW() BETWEEN Date1 AND Date2;

answered Nov 19 '20 at 15:01

Ryan Howard

9
4

falfab · Answer 3 · 2020-11-19T23:04:58.380

The function GETDATE() can be used to retrieve the current month.

The function DATEADD(datepart,number,date) can be used to perform operations on dates. For more info look at the official docs

Thus, to retrieve the records from two months before (-2) the current month you can use the following:

DATEADD(month, -2, GETDATE())

In conclusion an example query to select all records that were in the month two months from the current month:

SELECT * FROM table 
   WHERE MONTH(month_column) = DATEADD(month, -2, GETDATE())

sources:

Pls add little explanation to the answer. – Koushik Roy Nov 19 '20 at 15:59 — Koushik Roy, Nov 19 '20 at 15:59

score 0 · Answer 4 · answered Nov 19 '20 at 15:29

You can have the month part in a variable and then it can be used in the Where clause to filter the month part of the date value is equal to the varoable value.

Query

Declare @month as int;
Set @month=datepart(month, getdate()) - 2;

Select * from yourTableName
Where month(dateCol) = @month;

How to retrieve records that are from two months from the current date

4 Answers4