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?
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