cakephpcakephp-3.xcakephp-3.6

CakePHP 3.6 - Find All Records Created Between Two Dates


I am looking for all records between two dates

My variables

 $start = '01/01/2009';
 $end = '07/24/2019';

I have tried

$gross = $this->CartOrders->find('all')->where(['placed >=' => $start])->andWhere(['placed <=' => $end])->all();

Query Snippet for above

... FROM cart_orders CartOrders 
WHERE (placed >= :c0 AND placed <= :c1) 
[params] => Array ( 
     [:c0] => Array ( [value] => 01/01/2009 [type] => datetime [placeholder] => c0 ) 
     [:c1] => Array ( [value] => 07/24/2019 [type] => datetime [placeholder] => c1 ) )

Results in

 Cake\ORM\ResultSet Object ( [items] => Array ( ) )

I have also tried

$gross = $this->CartOrders->find('all')->where(function($exp) use($start,$end) {
        $exp->lte('placed', $end);
        $exp->gte('placed', $start);
        return $exp;
    })->all();

I also have tried

$gross = $this->CartOrders->find('all')->where(function($q) use($start,$end) {
        return $q->between('CartOrders.placed', $start, $end, 'date');
    })->all();

Any ideas on how I can accomplish this?


Solution

  • This turned out to be a date format issue.

    The following solved my problem.

    $start = '01/01/2009';
    $end = '07/24/2019';
    
    $start = DateTime::createFromFormat('d/m/Y', $start);
    $end = DateTime::createFromFormat('d/m/Y', $end);
    
    $gross = $this->CartOrders->find('all')->where([
         'placed >=' => $start->format('Y-m-d')
    ])->andWhere([
         'placed <=' => $end->format('Y-m-d')
    ])->all();
    

    This link helped

    PHP convert date format dd/mm/yyyy => yyyy-mm-dd