MySQL – SELECT * FROM table WHERE date = TODAY

mySQL SELECT WHERE date = today
mySQL SELECT WHERE date = today

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.


18 Comments

  • Hugh

    May 26, 2012

    Cheers mate, was pulling my hair out on this one as well!!!…

    Reply
  • Dominik Hahn

    June 12, 2012

    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

    Reply
  • Oliver

    June 13, 2012

    You can even calculate with CURDATE():
    SELECT * FROM TABLE WHERE `DATE` >= CURDATE()-3;

    Reply
    • Oliver

      June 14, 2012

      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)

      Reply
    • keith

      January 9, 2013

      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.

      Reply
      • bandertron

        January 19, 2013

        Thanks for your input Keith!

        Reply
      • Marco

        July 29, 2013

        Yep, well done “mysql manual” is not very clear for this point

        Reply
  • Zac Ball

    July 13, 2012

    Thanks for the little extra Oliver. That just now really came in handy.

    Reply
  • Pulast Labs

    September 1, 2012

    This is very useful.

    Reply
  • Jayden Bradow

    September 13, 2012

    Here’s the string I used to grab stuff that was created today..
    SELECT * FROM myTable WHERE myDateColumn >= CURDATE()

    Reply
  • Izrada sajta

    November 22, 2012

    Thank you so much, so simple and useful! :)

    Reply
  • Richard Reijmers

    December 6, 2012

    Nice one, exactly what I was looking for!! Cheers!

    Reply
  • ben

    January 14, 2013

    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!!

    Reply
    • bandertron

      January 19, 2013

      Thanks for the heads up Ben. Weirdly enough i stumbled on it just before reading your comment!
      Glad the the query helped :)

      Reply
  • sairam

    March 16, 2013

    thanks , it helped me in time.

    Reply
  • bob

    June 1, 2013

    This helped me quite a bit as well. Thank you!

    Reply
  • Bongo

    July 18, 2013

    Thank you so much…this helped me a lot

    Reply
  • Nick Jones

    November 4, 2013

    hi bandertron, thank you very very much. You saved my day.

    Reply

Leave a Reply