CodeIgniter query to search a datetime column by submitted year, month or day value

Question

I have a trips table which stores trip details. One of the field stores date and the type is DATETIME . I am creating a search query by date.

$q can be
$q = 2012   when searching for years
$q = 1-12   when searching for months
$q = 1-31   when searching for days

But when searching, say for 02 month in the tabe and there are many rows some of them being 2002-02-13 04:44:48, 2013-02-13 04:44:48, 2013-02-13 02:44:48 etc, all of them will be displayed because all of them contain 02 in them. But my intention is to get the row with 02 in the month . How can I do it and what will be the appropriate way.

function search($q){

    $this->db->select('*');
    $this->db->from('trips');
    $this->db->like('date',$q);

    // Execute the query.
    $query = $this->db->get();

    // Return the results.
    return $query->result_array();
}

I could accomplish it by storing day, month, year in different fields, But is there anyway to do it in the way I have stated.

Answer 1

try using native sql query you can do

$month = 02;
$sql = "SELECT MONTH(date) FROM notification where MONTH(date) = ?;";
$query = $this->db->query($sql,array($month));

MySQL has functions like , month,day,and year to use with datetime fields.