changing datetime to date for comparison in codeigniter

Question

I have a table with a datetime variable and it talble the values are likewhich has records of the format 2015-06-22 22:30:00.030.

How can I cast the datetime to a date format in codeigniter so that i can compare it with a date entered in a form (excluding the time)? This is my query and it gives me this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'As Date) > '%2015/05/31%' AND merchant_transactions.CAST(datetime As Date) < '%2' at line 7

    $search_where = "merchant_transactions.CAST(datetime As Date) > '%".$from."%' AND merchant_transactions.CAST(datetime As Date) < '%".$to."%'";

   $this->db->where($search_where);
   return $this->db->get();

Answer 1

Another option is to use the SQL BETWEEN function to compare dates but you'd need to convert the incoming dates to the date format: YYYY-MM-DD

$from = date('Y-m-d', strtotime($from_date));
$to = date('Y-m-d', strtotime($to_date));
$search_where = 'CAST(merchant_transactions.datetime As Date) BETWEEN "'.$from.'" AND "'.$to.'"';

Answer 2

You can use strtotime to parse input. I also think that your SQL should be changed a bit.

$time = '2015/05/31';
$from = strtotime($time);
$m_from = date('m', $from);
$d_from = date('d', $from);
$y_from = date('Y', $from);

$to = strtotime($time);
$m_to = date('m', $to);
$d_to = date('d', $to);
$y_to = date('Y', $to);

 $search_where = "CAST(merchant_transactions.datetime As Date) > '".$y_from."-".$m_from."-".$d_from."' AND CAST(merchant_transactions.datetime As Date) < '".$y_to."-".$m_to."-".$d_to."'";