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
.
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.)