Showing purchases made within the previous 8 months using SQL

I am trying to find purchases made within the previous 8 months. This does not include the current month but starting 1 month before the current month up to 8 months.

I have figured out how to find the purchases made in the last 8 months (query below), but how do I exclude that current month.

SELECT prod_id,
         customer_name,
         purchase_date
FROM puchase_orders
WHERE purchase_date > (DATEADD(m, -6, GETDATE()))

 
Jintonix415Asked:
Who is Participating?
 
AnujConnect With a Mentor SQL Server DBACommented:
For last 6 months excluding previous month

SELECT prod_id,
         customer_name,
         purchase_date
FROM puchase_orders
WHERE purchase_date BETWEEN  DATEADD(mm,-7,CONVERT(VARCHAR,GETDATE(),112)) AND DATEADD(mm,-1,CONVERT(VARCHAR,GETDATE(),112))
0
 
AnujSQL Server DBACommented:


SELECT prod_id,
         customer_name,
         purchase_date
FROM puchase_orders
WHERE purchase_date BETWEEN  DATEADD(mm,-7,GETDATE()) AND DATEADD(mm,-1,GETDATE())
0
 
AnujSQL Server DBACommented:
Use between, for retrieving 8 months data from previous month use  purchase_date BETWEEN  DATEADD(mm,-9,GETDATE()) AND DATEADD(mm,-1,GETDATE())
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jintonix415Author Commented:
anujnb. thank you I think that worked. But quick question, what is the 112 value and why did you need to add Convert?
0
 
AnujSQL Server DBACommented:
Just to remove the time part.
 
0
 
sachinpatil10dCommented:
Try this
SELECT prod_id,
         customer_name,
         purchase_date
FROM puchase_orders
WHERE purchase_date between convert(datetime, convert(nvarchar,(MONTH(getdate())-8)) + '-01-' + CONVERT(nvarchar,YEAR(getdate())))
and convert(datetime, convert(nvarchar,(MONTH(getdate())-1)) + '-01-' + CONVERT(nvarchar,YEAR(getdate())))

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.