Syntax Error: line 0, col 152: Error: Expected =, <, <=, <>, >, >=, !=, got 'BETWEEN'

Question

When I try the following code:

return $this->createQueryBuilder('e')
    ->select('e.user_id', 'e.year', 'e.week_number', 'e.approved_by')
    ->where('e.user_id = :userID')
    ->andWhere('(e.year * 100 + e.week_number) BETWEEN :startDate and :endDate')
    ->setParameter('userID', $userID)
    ->setParameter('startDate', ($startYear * 100 + $startWeek))
    ->setParameter('endDate', ($endYear * 100 + $endWeek))
    ->getQuery()
    ->getResult()
;

I get the error [Syntax Error] line 0, col 149: Error: Expected =, <, <=, <>, >, >=, !=, got 'BETWEEN'.

If I print my query, it looks like this:

dql: "SELECT e.user_id, e.year, e.week_number, e.approved_by FROM App\Entity\... e WHERE e.user_id = :userID AND ((e.year * 100 + e.week_number) BETWEEN :startDate and :endDate)"

If I use this query in SSMS and replace the variables with the actual data. It all works perfectly fine.

My variables are have the following value:

$startYear = 2021;
$startWeek = 52;
$endYear = 2022;
$endWeek = 05;

// :startDate = 202152;
// :endDate = 202205;

The calculations probably break the query builder. Mostly because I do similar query's: ->andWhere('e.week_number between :startWeek and :endWeek') that do indeed work. I don't know how to fix them or why this happens.

Im using Symfony 4.4. SQL Server for my database.

Answer 1

This comment of Zhorov fixed my problem.

You may try to change the BETWEEN condition with ->andWhere('(e.year * 100 + e.week_number) >= :startDate') and ->andWhere('(e.year * 100 + e.week_number) <= :endDate')

Still quite weird the query builder can't handle it. I mean not much has changed and SQL Server itself finds it all good. If someone can explain how this works, I probably vote it as an answer.