[SOLVED] How to use SQL Funtions like WEEK() in Typo3 (Extbase Repository)

How to use SQL Funtions like WEEK() in Typo3 (Extbase Repository) - Doctrine-DBAL?

I want to use the WEEK() function on a datetime field to filter fo a specific week.

Im using

TYPO3 v11.5
Extbase

Inside my Repository I have

$queryBuilder = $this->connectionPool->getQueryBuilderForTable($this->getTableName()); 
$queryBuilder ->select('*'); 

$queryBuilder->where(
    $queryBuilder->expr()->eq('week', $queryBuilder->createNamedParameter($pWeek));
);

I tried using ->addSelect('WEEK(myDateTime) AS week') but this results in 'WEEK(start)' AS week. (Where ' stands for ` )

Giving an SQL Syntax Error because there is no field 'WEEK(myDateTime)'

I know I can use the expression builder for some Aggregate functions. Also I could get the Date form my $pWeek with php and just compare the two Dates.

But there must be an easy way to use such SQL functions?

Solution

You can use ->addSelectLiteral() for this.