Select Statement for a Specific Date

[jsnull]

Hi ... I have a datetime field in my dB such as
yyyy-mm-dd hh:mm:ss.

How would I write a MySql statement in PHP to select all dates from this dB for a specific date of yyyy-mm-dd regardless of the hours, minutes and seconds.

Jim Null

http://www.jimnull.com

 

[skiflyer]

Are you storing these as strings?
WHERE field LIKE '2006-05-02%'; or are you storing them as datetime allowing you to specify ranges like

WHERE field > '2006-05-02 00:00:00' AND field < ' 2006-05-02 11:59:59';

Or perhaps there're built in range functions for datetime fields? Heck if I know about those though, I use postgresql.

 

[towerbase]

I think this should have been posted in the MySQL forum as the question is about MySQL and not PHP. It would also help to specify which version of MySQL you are using.

Try something like

SELECT * FROM table WHERE DATE(field)='2006-06-25'

Andrew
Hampshire, UK

 

[jpadie]

where date(datetimefield) = 'yyyy-mm-dd'

 

[jsnull]

Thank you - very much appreciated ... your replies have been very helpful in getting me to where I finally ended up. Here's what I came up with where I am getting user input for a date they want the actual records for:

$getdate = $_POST['UserSuppliedDate'];
$timestamp_get = strtotime($getdate);
  $timestamp_future  = $timestamp_get + (60*60*24*1);
 
$future_date = date('m/d/Y', $timestamp_future);   $get_date = date('m/d/Y', $timestamp_get);
  
  $future_date = formatDate($future_date); // a little function of my own that formats the date to that I am saving in dB
  $get_date = formatDate($get_date);

$query =  mysql_query("SELECT * FROM `data` WHERE `regdate` >= '$get_date' AND `regdate` < '$future_date'");

Jim Null

http://www.jimnull.com