Simple SELECT COUNT SQL query error

Question

I'm trying to get the total number of rows using this SQL query, the result should be = 1

SELECT COUNT(id) FROM offer_process WHERE uid = 103 AND date = '2014-08-20'

I don't know exactly how to get the total number of rows from a date (2014-08-20) in this case.

Answer 1

You have a datetime column.
To check only date part, use DATE() MySQL function

SELECT COUNT(id) FROM offer_process WHERE uid = 103 AND DATE(date) = '2014-08-20'

Also, if you have an index on your date field, you should avoid using DATE/TIME MySQL functions. Instead, you can use WHERE date LIKE '2014-08-20%' for better performance

Answer 2

You may try this:

SELECT COUNT(id) FROM offer_process WHERE uid = 103 
AND DATE_FORMAT(`date`, "%Y-%m-%d") = '2014-08-20'

or you may try using date

SELECT COUNT(id) FROM offer_process
WHERE uid = 103 AND date(`date`) = '2014-08-20'