Hi,

I am trying to find all reviews on a certain day and the field is datetime. I tried this:
SELECT FROM reviews WHERE rdate > 2005-05-10 00:00:00
and this
SELECT FROM reviews WHERE rdate = 2005-05-10
but no luck.

Any help?

Thanks.

    Treat the dates as strings, i.e.:

    SELECT * FROM `reviews` WHERE rdate > '2005-05-10 00:00:00';

SELECT * FROM `reviews` WHERE rdate = '2005-05-10';

    (when I say treat them as strings, I mean enclose them in quotes).

    That should fix any problems with comparing dates you seem to be having.

    EDIT: Also, there is a forum devoted to "Database" issues, aka SQL issues. Some of us knowledgeable (ha, ok, not me, but other posters are knowledgeable...) folk don't always know a lot of SQL. The regulars that browse the SQL form most likely do, however 😉 Just FYI in the future, in case you get stuck on more SQL coding.

      thank you very much!

      I'm still having problems using:
      SELECT * FROM reviews WHERE rdate = '2005-05-10';

      I think I need to find how to do the query to just check date and not worry about time,..?

        From 4.1.1 there is DATE() to extract the date part of a datetime, would that help? With earlier versions I can think of quite a lot of other ways to get the date part out, e.g. DATE-FORMAT() or maybe use BETWEEN instead, though I would not know which one is most efficient.

          To check for a certain day in a DATETIME field (i.e. ignore the time) you can do it one of two ways:

          SELECT * FROM `reviews` WHERE rdate LIKE '2005-05-10 %';

          OR

          SELECT * FROM `reviews` WHERE rdate >= '2005-05-10 00:00:00' AND rdate < '2005-05-11 00:00:00';

          Try that. I'm pretty sure that'll work.

            Write a Reply...