CodeIgniter Model Date Range-CodePudding

I have been struggling for a few weeks. Recently I posted a question here CodeIgniter Datatables Server Side Date Range using Datepicker but no luck.

Then I decide to isolate the problem by testing the Codeigniter Model and seems like there is a problem there.

Below is the code and image.

Codeigniter Model

function get_allbillinglistByDate($startdate,$enddate){
    $data = array();
    $sdate = "09/01/2020";
    $edate = "11/01/2020";
    $this->db->from('invoices');
    $multipleWhere = ['invoices.Approved' => 1,'invoices.xero' => 0];
    $this->db->where($multipleWhere);
    //$this->db->where('Invoice_Date BETWEEN "'. date('m-d-Y', strtotime($sdate)). '" and "'. date('m-d-Y', strtotime($edate)).'"');
    $this->db->where('Invoice_Date >=', date('m-d-Y', strtotime($sdate)));
    $this->db->where('Invoice_Date <=', date('m-d-Y', strtotime($edate)));
    $Q = $this->db->get();
    
    if ($Q->num_rows() > 0){
        foreach ($Q->result_array() as $row){
            $data[] = $row;
        }
    }
    $Q->free_result();
    return $data;
}

Here is the screenshot Date Column where only 2020 record should show but it shows 2021 too

Not sure where things are going wrong the date Column where only 2020 record should show but it shows 2021 too

Please advise.

CodePudding user response：

Although MySQL tries to interpret values in several formats, date parts must always be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98'). To convert strings in other orders to year-month-day order, the STR_TO_DATE() function may be useful.

Ref: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html

So, change your code as below

date('Y-m-d', strtotime($sdate)

CodePudding user response：

The answer of Raj is correct. You can tweak it according to your expected format like below

date('d/m/Y', strtotime($sdate));
date('d/m/Y', strtotime($edate));

An alternative syntax of your method but you may need to tweak it a little. You should format date params and then send them to this method, you can format them as we described

function get_allbillinglistByDate($startdate,$enddate){
  $sql = "SELECT * FROM invoices where Approved = 1 AND xero = 0 AND DATE(Invoice_Date) BETWEEN ? AND ?"; 

  $data = array();
  array_push($data, $startdate);
  array_push($data, $enddate);

  $query = $this->db->query($sql, $dataArr);
  return (array)$query->result_array();
}

For more info check https://www.php.net/manual/en/function.date.php