How to find all dates between two dates in Zend Framework Query

Question

I need to find all dates between two dates; that is start date and end date. Here is my query but it is not doing what I need it to do.

In my table I have column name date_created which is in format like this 2011-06-09 06:41:10. I want to remove this portion 06:41:10 so I am applying

DATE(date_created)

After that as my datepicker is in this format 02/07/2012 I change the format through DATE_FORMAT() .

$start_date and $end_date are my variables coming for comparing and in format of 02/07/2012

$select = $DB->select()
->from('sms', array(
    'sms_id',
    'sms_body',
    'sms_sender',
    'sms_recipient',
    'date_created',
    'sms_type'))
->where('phone_service_id = ?', $phone_service_id)
->where("DATE_FORMAT(DATE(date_created), '%m/%d/%Y') >= ?",  $start_date)
->where("DATE_FORMAT(DATE(date_created), '%m/%d/%Y') <= ?",  $end_date)
->order("".$option_call_log." ".$option_call_log_asc_desc);

What am I missing in the query? Why is it not comparing $start_date and $end_date?
Forget about $option_call_log and $option_call_log_asc_desc.

Answer 1

If your dates are coming through in the format MM/DD/YYYY, then rather than getting MySQL to do all the work I'd convert them to YYYY-MM-DD HH:II:SS in PHP, which allows you to do a straightforward range based query in MySQL. So:

$start_date_formatted = date('Y-m-d', strtotime($start_date)).' 00:00:00';          
$end_date_formatted = date('Y-m-d', strtotime($end_date)).' 00:00:00';

$select = $DB->select()
    ->from('sms', array('sms_id','sms_body','sms_sender','sms_recipient','date_created','sms_type'))
    ->where('phone_service_id = ?', $phone_service_id)
    ->where("date_created >= ?",  $start_date_formatted)
    ->where("date_created <= ?",  $end_date_formatted)
    ->order("".$option_call_log." ".$option_call_log_asc_desc)
    ;

(If the dates in your date picker are actually in DD/MM/YYYY format then things are a little more fiddly.)

Answer 2

update change hours to include the last day $end_date_formatted = date('Y-m-d', strtotime($end_date)).' 23:59:59';