The Quick answer is:
SELECT * FROM myTable WHERE DATE(myDate) = DATE(NOW())
I was tearing my hair out trying to figure this the other morning and its really quite simple. I thought i may have use a day month year match or possibly use PHP but thankfully the guys at MySQL included this nice DATE() function which means you don’t have to worry about the hours, minutes and seconds being different. Simples!
MySQL DATE / TIME Functions
The functions used in thie MySQL query are:
* DATE() returns the date without time
* NOW() returns the current date & time (note we’ve used the DATE() function in this query to remove the time)
For more information about MySQL Date and Time functions on the official MySQL site.
Cheers mate, was pulling my hair out on this one as well!!!…
Instead of DATE(NOW)) you can simply use CURDATE() 🙂
See: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_curdate
You can even calculate with CURDATE():
SELECT * FROM TABLE WHERE `DATE` >= CURDATE()-3;
Sorry, I have to correct my last comment. The above doesn’t work. The correct query looks like this:
SELECT * FROM TABLE WHERE `DATE` >= DATE_SUB(CURDATE(),INTERVAL 3 DAY)
But that loses the date formatting, so you can do this:
DATE(my_date) = DATE(CURDATE()-2)
which would find anything from 2 days ago.
Thanks for your input Keith!
Yep, well done “mysql manual” is not very clear for this point
Thanks for the little extra Oliver. That just now really came in handy.
This is very useful.
Here’s the string I used to grab stuff that was created today..
SELECT * FROM myTable WHERE myDateColumn >= CURDATE()
Thank you so much, so simple and useful! 🙂
Nice one, exactly what I was looking for!! Cheers!
Thanks for the help with the MySQL query.
By the way, your photos in the sidebar are pointing to an IP on your local network. I wanted to see those bridge pics!!
Thanks for the heads up Ben. Weirdly enough i stumbled on it just before reading your comment!
Glad the the query helped 🙂
thanks , it helped me in time.
This helped me quite a bit as well. Thank you!
Thank you so much…this helped me a lot
hi bandertron, thank you very very much. You saved my day.