SQL Query to select data based on year and month

Question

I'm writing an ecommerce app and im writing a script to track the sales of items according to year/month.

I used this query here to get the distinct year-month dates from the table.

SELECT DISTINCT CONCAT(YEAR(date),'-',MONTH(date)) FROM product_purchases WHERE product_id = 1

Which would give me an output like so

2017-11
2017-12

What im trying to accomplish next is to select data that match that year and month, for example 2017-11.

I've tried this query which returned 0 rows

SELECT * FROM product_purchases WHERE DATE(date) = '2017-12'

What would be the right way to go about doing this?

Answer 1

Replace your where statement with this

CONCAT(YEAR(date),'-',MONTH(date)) = '2017-12'.

i.e.

SELECT * FROM product_purchases WHERE CONCAT(YEAR(date),'-',MONTH(date)) = '2017-12'

You can do this ultimately.

Select * from (SELECT DISTINCT CONCAT(YEAR(date),'-',MONTH(date)) as NewDate,Product_Id,
Product_Name FROM product_purchases WHERE product_id = 1) where NewDate='2017-12'