In this quick technical solution post, I will tell you how to query a date from a datetime field in PHP.

If you read the post PHP get date 7 days ago, you know that my project today called for querying a MySql database on a date 7 days in the past. Well, having gotten a string that equals the date from 7 days ago, now I need to query the MySql database with that date. However, the field is set at datetime. Thus, querying on the date like you see in the code below yields no results:

$oneweekago='2011-09-26';
mysql_query("SELECT * FROM table WHERE registration = '$oneweekago' ")  // not working because time is in the datetime field

As you see in the coding comment above, this query does yield any results because it also wants a time. So, we will use the MySQL between syntax to get the information that we want, which is all users who registered on the date of $oneweekago.

To do this, we create two new variables. The first will be the time at the very start of that day one week ago and the second will be the last moment of that day. As you see in the code below, we add these two variables and then we change the MySQL query to use the between syntax.

$oneweekago='2011-09-26';
$date1=$oneweekago." 00:00:00";
$date2=$oneweekago." 23:59:59"; 
mysql_query("SELECT * FROM table WHERE registration between '$date1' and '$date2'  ")  // this now gets all users who registered that day

Query Date in DateTime Field

There are many people discussing the best way to get the date from a datetime in MySQL/PHP. I found this way to be quick and easy. I hope that you did too.

Cheers, Richard