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?

Solution 1

To find records of some month, update your where clause to :

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

Solution 2

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'

Solution 3

This will help you

WHERE YEAR(date) = 2017 AND MONTH(date) = 12

Solution 4

Of course your query returns 0 row. Problem comes with your condition. It should be:

SELECT * FROM product_purchases WHERE YEAR(date) = '2017' AND MONTH(date) = '12'

Solution 5

If you want data just to filter by year and month, use date_format on date column to match with desired year and month.

Example:

where date_format( date_column, '%Y-%m' ) = '2017-12'

If you want to summarize data by year and month, use group by on the format.

Example:

group by date_format( date_column, '%Y-%m' )

View more solutions