Date format in query


#1

I am moving an old site from 2.x to 3.x
In cakephp 2.x I could do :
$conditions[‘Schedule.debut BETWEEN ? AND ?’] = array(date(“Y-m-d”, strToTime($start)), date(“Y-m-d”, strToTime($end)));

but this does not work in 3.x
neither ->where(['Schedules.debut BETWEEN ? AND ?' => date("Y-m-d", strToTime($startDate)), date("Y-m-d", strToTime($endDate))])
or ->where(function (QueryExpression $exp, Query $q) use ($start, $end) { return $exp->between('debut', $start, $end); })

I tried several formats for $start and $end including :
a raw 2019-01-31
or Time::parse(‘2019-01-31’)

If someone knows what I’m doing wrong, he is welcome :slight_smile


#2

Try formatting it into a variable prior to using the query.


#3

I believe you need to tell the between call what type the data is:

->where([
    function (QueryExpression $exp) use ($start, $end) {
        return $exp->between('debut', $start, $end, 'date');
    },
]);

#4

Thank yoy for trying to help :
I have this message :

Argument 1 passed to App\Controller\Admin\BilletsController::App\Controller\Admin{closure}() must be an instance of App\Controller\Admin\QueryExpression, instance of Cake\Database\Expression\QueryExpression given, called in /home/wmcmiinz/www/dev/tgcmc/vendor/cakephp/cakephp/src/Database/Expression/QueryExpression.php on line 665 and defined [APP/Controller/Admin/BilletsController.php, line 110]


#5

You need to have use Cake\Database\Expression\QueryExpression at the top of the file.


#6

does not work.
temporarily solved it this way :
‘debut >’ => date(“Y-m-d”, mktime(0, 0, 0, 0, 1, $year)),
‘debut <’ => date(“Y-m-d”, mktime(0, 0, 0, 11, 31, $year)),

Thank you all of you for trying to help