Codeigniter从数据表日期时间格式中选择日期

Question

I have a table with a date in datetime format, something like this : 2016-03-18 18:42:22

I want to be able to select data by the day, meaning all data for the 2016-03-18.

For example, regardless of the hours, minutes and seconds.

I tried to do this :

$yesterdaydate = date('Y-m-d', strtotime('-1 days'));
$this->db->select("id_status, id_service, active");
            $this->db->select("DATE_FORMAT(date, '%Y %m, %d') as formatted_date", FALSE);
            $this->db->from('status');
            $this->db->where('formatted_date', $yesterdaydate);
            $this->db->order_by("date", "desc");

$query = $this->db->get();

return $query->result(); 

But it doesn't seem to work. I have an error that there's not formatted_date in my table.

Answer 1

$yesterdaydate = date('Y-m-d', strtotime('-1 days'));

use this in where condition

WHERE formatted_date>='$yesterdaydate 00:00:00'