Getting data posted in between two dates

Question

How can I retrieve data from the database by querying records between two dates using CodeIgniter's activerecord?

Exenterate

please explain further, and if you have tried anything, please post the code. – Amenity

I have no idea what you're talking about – Mettlesome

Are you trying to pull data from your database between two dates? – Preciousprecipice

yes I am trying to pull data from my database between two dates using codeigniter quries – Exenterate

Answer 1

This looks like what you need:

$this->db->where('order_date >=', $first_date);
$this->db->where('order_date <=', $second_date);
return $this->db->get('orders');

Mettlesome

@ThorpeObazee what if i have two number and want to that entered number is in between or not? – Dereism

Answer 2

Try This:

$this->db->where('sell_date BETWEEN "'. date('Y-m-d', strtotime($start_date)). '" and "'. date('Y-m-d', strtotime($end_date)).'"');

Hope this will work

Hiding

Answer 3

This worked great for me

$this->db->where('sell_date BETWEEN "'. date('Y-m-d', strtotime($start_date)). '" and "'. date('Y-m-d', strtotime($end_date)).'"');

Ethanol

Answer 4

if your date filed is timestamp into database then this is the easy way to get record

$this->db->where('DATE(RecordDate) >=', date('Y-m-d',strtotime($startDate)));
$this->db->where('DATE(RecordDate) <=', date('Y-m-d',strtotime($endDate)));

Schleiermacher

Using DATE function in where clauses causes your indexing to fail. Then you have to bear performance issues. – Offstage

Answer 5

This worked for me:

$this->db->where('RecordDate >=', '2018-08-17 00:00:00');
$this->db->where('RecordDate <=', '2018-10-04 05:32:56');

Trireme

Answer 6

If you want to compare SQL dates, you can try this:

$this->db->select();
$this->db->from('table_name');
$this->db->where(' date_columnname >= date("'.$from.'")');
$this->db->where( 'date_columnname <= date("'.$to.'")');

That worked for me (PHP and MySQL).

Sartor

Answer 7

Just simply write BETWEEN '{$startDate}' AND '{$endDate}' in where condition as

->where("date BETWEEN '{$startDate}' AND '{$endDate}'")

Figment

Answer 8

May this helpful to you.... With Join of Three Tables

public function get_details_beetween_dates()
    {
        $from = $this->input->post('fromdate');
        $to = $this->input->post('todate');

        $this->db->select('users.first_name, users.last_name, users.email, groups.name as designation, dailyinfo.amount as Total_Fine, dailyinfo.date as Date_of_Fine, dailyinfo.desc as Description')
                    ->from('users')
                    ->where('dailyinfo.date >= ',$from)
                    ->where('dailyinfo.date <= ',$to)
                    ->join('users_groups','users.id = users_groups.user_id')
                    ->join('dailyinfo','users.id = dailyinfo.userid')
                    ->join('groups','groups.id = users_groups.group_id');

        /*
        $this->db->select('date, amount, desc')
                 ->from('dailyinfo')
                 ->where('dailyinfo.date >= ',$from)
                 ->where('dailyinfo.date <= ',$to);
        */

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

        $array['userDetails'] = $q->result();
        return $array;
    }

Higinbotham

Answer 9

$query = $this->db
              ->get_where('orders',array('order_date <='=>$first_date,'order_date >='=>$second_date))
              ->result_array();

Gertrudegertrudis

I feel like the <= and >= might be the wrong way around, but I don't know what values they actually hold. – Schleswigholstein

Answer 10

if you want to force using BETWEEN keyword on Codeigniter query helper. You can use where without escape false like this code. Works well on CI version 3.1.5. Hope its help someone.

if(!empty($tglmin) && !empty($tglmax)){
        $this->db->group_start();
        $this->db->where('DATE(create_date) BETWEEN "'.$tglmin.'" AND "'.$tglmax.'"', '',false);
        $this->db->group_end();
    }

Fineable

Answer 11

If you are using DATETIME datatype then you can use:

$this->db->where('order_date BETWEEN "'. date('Y-m-d H:i:s', strtotime($start_date.' 00:00:00')). '" and "'. date('Y-m-d H:i:s', strtotime($end_date.' 23:59:59')).'"');

Healthy

Answer 12

$start_date = $this->input->post('startDate');
$end_date  = $this->input->post('endDate');
    
$data['total_retailer'] = $this->company_model->where(
                    array(
                        'DATE(company_created_date) >=' => $start_date,
                        'DATE(company_created_date) <=' => $end_date,
                    )
                )

Anchoress