php/mysql - date_format and the time portion

Question

Apologies if this has already been answered many times, but I was unable to find the answer and I was flummoxed.

I have a mysql query which seemingly outputs the result I want when I run it in the database - however when I run the query through PHP, it does not output correctly.

In the database, dates are stored like this:

2009-08-13T00:00:00

What I want to do is NOT display data that is older than todays date. So, in the where clause is do this:

  WHERE dateField1 >= DATE_FORMAT(now(),'%Y-%m-%d') AND dateField2 >= DATE_FORMAT(now(),'%Y-%m-%d')

My intention is to strip the time portion, as I was struggling to find a way to convert the time part of now() to midnight.

This seems to work when I run it on PHPMyadmin, but when I run the php script it does not.

Any ideas?

Many Thanks!

Answer 1

This seems to work when I run it on PHPMyadmin, but when I run the php script it does not.

You're not using something like sprintf to build the string are you? If so, you need to escape the % with another %

In any case, you can use the function DATE()

WHERE `dateField1` > DATE(NOW())

Answer 2

nickf is right. I agree with that.

In case of using sprintf() function to make a SQL call, you may turn your query into this:

  $query = sprintf("SELECT * FROM SOME_TABLE WHERE id=%s AND dateField1 >= DATE_FORMAT(now(),'%%Y-%%m-%%d') AND dateField2 >= DATE_FORMAT(now(),'%%Y-%%m-%%d')", $id);
//Then submit $query for SQL query processing...

PHP see % as type specifier when using sprintf().

To escape this, you need to add one more % in front of MySQL format string, i.e., %%Y or %%m or %%d. The actual output to MySQL query would become %Y or %m or %d again.

Answer 3

Here is a good site to help you format date in mysql http://www.mysqlformatdate.com