Selecting a date in phpmyadmin

Dolemite50

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.

bradgrafelman

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.

Dolemite50

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,..?

xblue

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.

bradgrafelman

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 %';

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.