Reputation: 19857
so right now I'm storing a the date of each search done on my site like this
2011-06-07 21:44:01
now I'd like to execute a query to select all values where the date is equal to whatever, but only the year / month and day are taken into account, so it would be like
mysql_query("SELECT tag from tags WHERE date = '2011-06-07'");
but it shouldn't take into account the exact time (hour minute seconds), is there any way to do this?
Upvotes: 36
Views: 93751
Reputation: 1
For me the following query works without a problem:
SELECT <col1>, <col2>, <col3>
FROM `tablename`
where date like "2025-03-06%"
limit 20
Upvotes: 0
Reputation: 9
I know the answer am about to post is long overdue but it can help someone who may experience the same problem.
For my case I used:
SELECT *
FROM tablename
WHERE dateCol >= date("2017-11-01")
AND dateCol < date("2017-11-01") + INTERVAL 1 DAY;
Its faster than using DATE() function.
If you are using a stored procedure, you could pass in the date as a string and supply it as an argument in place of ("2017-11-01")
Upvotes: 1
Reputation: 490657
You could use the DATE()
function.
SELECT `tag`
FROM `tags`
WHERE DATE(`date`) = '2011-06-07'
However, for better performance you could use...
WHERE `date`
BETWEEN '2011-06-07'
AND '2011-06-07 23:59:59'
Upvotes: 74
Reputation: 1330
mysql_query("SELECT tag from tags WHERE DATE(`date`) = '2011-06-07'");
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date
Upvotes: 1
Reputation: 3754
This should work:
mysql_query("SELECT tag from tags WHERE date LIKE '2011-06-07%'");
Upvotes: 7