WHERE DATE_FORMAT count problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pedroz
Forum Commoner
Posts: 99
Joined: Thu Nov 03, 2005 6:21 am

WHERE DATE_FORMAT count problem

Post by pedroz »

table_1
id, date, username
786, 2010-11-15 17:26:00, 22905035
787, 2010-11-29 18:26:01, 102380017
787, 2010-11-29 18:26:08, 102380017

$count = mysql_num_rows(mysql_query("SELECT `id` FROM `table_1` WHERE DATE_FORMAT(`date`,'%d%m%Y') = '".date('dmY')."'"));

considering today is 29-Nov-2010...
echo $count // ...2
should be 2 but it is not working...

anyone knows why?

thanks!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: WHERE DATE_FORMAT count problem

Post by Christopher »

Have you checked whether those date formats in PHP and SQL provide the same values. For example, do the codes provide leading zeroes or not?

You can also just do:

Code: Select all

$count = mysql_num_rows(mysql_query("SELECT `id` FROM `table_1` WHERE `date` = '".date('Y-m-d')."'"));
(#10850)
Post Reply