4

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?

Haider Ali
  • 918
  • 2
  • 9
  • 26

7 Answers7

4

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'
WorksOnMyLocal
  • 1,617
  • 3
  • 23
  • 44
3

DATE(date) will generate the full date format, which is not equivalent to '2017-12'. Try this instead:

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

Or this:

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

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

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

Nils
  • 806
  • 1
  • 9
  • 24
2

This will help you

WHERE YEAR(date) = 2017 AND MONTH(date) = 12
A.A Noman
  • 5,244
  • 9
  • 24
  • 46
Lima
  • 51
  • 7
1

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'
vietanhyt
  • 588
  • 3
  • 15
1

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' )
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
1

SELECT * FROM oc_customer WHERE MONTH(date_added) = 1 AND YEAR(date_added) = 2016

Mukesh
  • 73
  • 5