MySQL (now() - Interval 1 Month) for this year only

Question

I need some help with this.

I have the following SQL statement

SELECT * FROM table 
WHERE MONTH(ORDERDATE) = MONTH(NOW() - INTERVAL 1 MONTH)

The problem is that it is returning data from last month but for all years... Am I doing something wrong or is this a normal issue that people face with this function?

The problem I am facing is that if I use the YEAR(NOW()) the report I am writing will not show the data for 2016 when we hit 2017. I'm trying to write a 6 month sales history report.

Any guidance would be appreciated.

Added Information

SELECT * FROM DATA_WH.SALESORD_HDR WHERE MONTH(ORDERDATE) = MONTH(NOW() - INTERVAL 1 MONTH)

RETURNS....

'2015-08-14 00:00:00'

Answer 1

Try using DATE_SUB with BETWEEN:

SELECT *
FROM DATA_WH.SALESORD_HDR
WHERE ORDERDATE BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND
                        DATE_SUB(NOW(), INTERVAL 2 MONTH)

This avoids the problem of having to deal with boundary conditions when using MONTH and YEAR.

Edit:

The above query will return records whose order date is between one and two months old. If you want to identify orders from the previous calendar month, then you will have to do a bit more work. Try this query:

SELECT *
FROM DATA_WH.SALESORD_HDR
WHERE ORDERDATE >= STR_TO_DATE(CONCAT('01-', LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)), 2, '0'), '-', YEAR(DATE_SUB(NOW(), INTERVAL 1 MONTH))), '%d-%m-%Y') AND
      ORDERDATE <  STR_TO_DATE(CONCAT('01-', LPAD(MONTH(NOW()), 2, '0'), '-', YEAR(NOW())), '%d-%m-%Y')

The strategy here is to build the date boundaries (August 1 and September 1 of 2016, as of the time of writing this answer), using the ORDERDATE.

Here is a Fiddle showing this logic in action:

SQLFiddle