[RESOLVED] Using DATE in a DATETIME field

[twilson]

I am trying to SUM donations from a table by day using a SELECT statement. I've looked through the MySQL Manual and thought I remembered a statement similar to the following working:

SELECT campaign, SUM(amount) FROM donations 
  WHERE enteredTime = '2006-05-01' 
  GROUP BY campaign

enteredTime is a DATETIME field. This query executes, but returns no rows. To make it work I have to write:

SELECT campaign, SUM(amount) FROM donations 
  WHERE enteredTime LIKE '2006-05-01%' 
  GROUP BY campaign

Does MySQL not know how to use just the DATE portion of the field? I would like to be able to have it use the current date as follows and work, but this doesn't work either:

SELECT campaign, SUM(amount) FROM donations 
  WHERE enteredTime = CURDATE() 
  GROUP BY campaign

Is my second example seriously the best way to do this?

Thanks,
Tom

[bogu]

U can mak it this way too ...

$sql_txt = "SELECT campaign, SUM(amount) FROM donations ";
$sql_txt.= "WHERE UNIX_TIMESTAMP(enteredTime) BETWEEN ".mktime(0,0,0)." AND ".mktime(23,59,59)." ";
$sql_txt.= "GROUP BY campaign";

OR this way ...

$sql_txt = "SELECT campaign, SUM(amount) FROM donations ";
$sql_txt.= "WHERE DATE_FORMAT(enteredTime,'%Y-%m-%d') LIKE CURDATE() ";
$sql_txt.= "GROUP BY campaign";

OR any way u want ...

This is 4 u to choose...

[twilson]

That's exactly what I was looking for. Thanks so much!

Tommy