2

In my table dates are stored in the format "2010-08-26 09:00:00" so yyyy-mm-dd. I'd like to select all fields that have a date field that is "today", so between 2010-08-26 00:00:01 and 2010-08-26 23:59:59.

Just using larger and smaller than operators does not work, does MySQL have some built in date comparison functions that can do this?

EDIT: I should have mentioned dates are stored as datetime fields

stef
  • 26,771
  • 31
  • 105
  • 143
  • What about records with a date of exactly midnight? – Rowland Shaw Aug 26 '10 at 11:17
  • Fair enough, this is actually an in house planning tool and only office hour dates are entered (impossible to add before 8AM and after 8PM). My "between" values could reflect that but it makes no difference really. – stef Aug 26 '10 at 11:19
  • Just a hint, there is also a BETWEEN comparision function in MySQL. It can be used like this: WHERE my_date BETWEEN '2010-08-26 10:00:00' AND '2010-08-26 12:00:00'. It does not apply in this case though as there are better functions for this. (BETWEEN works for any string, number and date) –  Aug 26 '10 at 11:27

5 Answers5

7
... WHERE DATE(field)=CURDATE()

or

... WHERE DATE(field)=CURRENT_DATE

or 

... WHERE DATE(field)=CURRENT_DATE()

Pick one!

Vili
  • 1,599
  • 15
  • 40
  • Just for completness: I usually use DATE(field)=DATE(NOW()) which might be less performant but easier to remember. –  Aug 26 '10 at 11:25
2

I'd suggest [...] WHERE DATEDIFF(CURDATE(),date_field) = 0 [...]

pharalia
  • 709
  • 4
  • 6
  • Thanks for this information. I had been searching for this function (though not exactly the same problem) and your answer was helpful for me :) – Ankur Feb 28 '13 at 10:42
1

use function DATE()

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date

Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
1

Just a recommendation for the future: storing a date in a varchar column is almost always a very bad idea.

0

Using the DATE() function you should be able to construct a value for comparison:

DATE(2010-08-26 12:13:24)

will return just the date section for you ie. 2010-08-26.

simnom
  • 2,590
  • 1
  • 24
  • 34