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

Question

I need to find all dates between two dates; This is the start date and end date. Here is my request, but it does not do what I need.

My table has the column name date_created , which is in this format 2011-06-09 06:41:10 . I want to remove this part 06:41:10 , so I apply

 DATE(date_created) 

After that, when my datepicker is in this format 02/07/2012 , I change the format to DATE_FORMAT() .

$start_date and $end_date are my variables, going for comparison and in the format 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 request? Why doesn't this compare $ start_date and $ end_date?
Forget about $option_call_log and $option_call_log_asc_desc .

Answer 1

If your dates are in MM / DD / YYYY format, then instead of getting MySQL to do all the work, I would convert them to YYYY-MM-DD HH: II: SS in PHP, which allows you to make a simple range-based query in MySQL. So:

 $start_date_formatted = date('Ym-d', strtotime($start_date)).' 00:00:00'; $end_date_formatted = date('Ym-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 everything will be a little weirder.)

Answer 2

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