how to test date and datetime with mysql

Question

hi guys, My table is using a datetime (YYYY-MM-DD HH:MM:SS) and i need to display today's entries.

my code is only :

SELECT   * 
FROM     table 
WHERE    date = '$date' 
ORDER    BY score DESC

with

$date = date("Y-m-d");

well, as expected it doesnt work :| you guys have a solution here ?

Answer 1

Your date is "2009-12-19" (or something like that, depending on the day), which is interpreted as "2009-12-19 00:00:00".

In your database, you probably don't have any date that's exactly equal to that one, by the second : your dates are like "2009-12-19 12:15:32".

A solution is to compare like this :

select *
from table
where date >= '2009-12-19'
    and date < '2009-12-20'

Which will be interpreted as :

select *
from table
where date >= '2009-12-19 00:00:00'
    and date < '2009-12-20  00:00:00'

And, if you don't want to do the math to get the date of the following date, you can use the adddate function :

select *
from table
where date >= '2009-12-19'
    and date < adddate('2009-12-19', interval 1 day)

So, in your case, something like this should do the trick :

select *
from table
where date >= '$date'
    and date < adddate('$date', interval 1 day)
order by score desc

Answer 2

You probably want to format the data when you select it:

SELECT *, DATE_FORMAT(date, '%Y-%m-%d') AS dateformat FROM table
WHERE dateformat = '$date' ORDER BY score DESC

Answer 3

You are comparing datetime and date expression, that is why its not working. Use Date() method to return the date part from datetime and then do the comparison. WHERE DATE(date) = '$date' should do. You might have to use aliases to handle this name collision.

Answer 4

Following from Pascal Martin, you could extract the date part from the date+time field:

SELECT * FROM table WHERE DATE(date) = '2009-12-19'

Source: MySQL - Date and Time Functions

Be aware however, that this query will not use an index on your date+time field, if you will be having one. (Stack Overflow: How does one create an index on the date part of DATETIME field in MySql)