CODEWITHSUNDEEP
mysqlcodeigniter
Piyush
Piyush

Reputation: 4007

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

Upvotes: 3

Views: 2032

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35603

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".

Upvotes: 1

Narendrasingh Sisodia
Narendrasingh Sisodia

Reputation: 21422

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

Upvotes: 10

Related Questions