phpmysqlcodeigniterquery-builderyearmonth

How to compare only the YEAR and MONTH of a date column using CodeIgniter's query builder

My model:

public function get_payscale()
{
    $this->db
        ->from('payscale P')
        ->join('employee E','E.employee_id = P.employee_id  ');
    $this->db->where('P.payscale_date',date('Y-m'));
    return $this->db->get()->result_array();
}

I want to compare only the month and year with the db, in which the format is y-m-d, where I want only to retrieve the y-m using the where clause.

Solution

  • Use like this :

    $this->db->where('EXTRACT(YEAR_MONTH FROM P.payscale_date)',date('Ym'));

    This query will extract the Year and month from the date given column like 201605 (2016-05-21)