Select records by month in where clause

问题:

My query to get data by month

$this->db->select('vehicle_make_and_model');
$this->db->join('tukai_drivers','tukai_drivers.driver_id=reqn_challans.dc_driver_id','left');
$this->db->where('dc_challan_date','MONTH(2)');
$this->db->order_by('dc_challan_id','desc');
$result=$this->db->get('reqn_challans')->result();

I am trying it via codeignitor like this

$this->db->where('dc_challan_date','MONTH(2)');

It did not work? What is wrong

Storing date like this

http://awesomescreenshot.com/0424q72ne5

回答1:

You need to update your where condition from

$this->db->where('dc_challan_date','MONTH(2)');

to

$this->db->where('MONTH(dc_challan_date)','2');

The actual function is like MONTH(date) so here you have MONTH(dc_challan_date) which is your column name

回答2:

In SQL databases you should NOT apply functions to data to suit the where clause so that you maximize the benefit of indexes (i.e. get better performance). For example, instead of using MONTH(dc_challan_date) you can achieve getting a month of data using the >= with < such as the following SQL:

select * from table
where dc_challan_date >= '2015-02-01'
and dc_challan_date < '2015-03-01'

The database optimizer can use an index on column dc_challan_date in that query. The example uses "sargable predicates".