Doctrine querybuilder DATE_FORMAT not working

Question

I'm having some problems with DATE_FORMAT inside a createQueryBuilder

My code:

$qb7Days = $repo->createQueryBuilder('R')
        ->select( 'R.createdAt' )
        ->where( "DATE_FORMAT(R.createdAt, '%Y-%m-%d') = :afterDays" )
        ->andWhere( 'R.cCurrentReviewState = :state' )
        ->andWhere( 'R.reminder = :reminder' )
        ->setParameter( 'afterDays', $after7Days )
        ->setParameter( 'state',  $oReviewStateNotVerified ) // not_verified
        ->setParameter( 'reminder',  0 ) // never sent any reminder
        ->orderBy( 'R.id', 'ASC' )
        ->getQuery();

But im getting

 [Doctrine\ORM\Query\QueryException]                                              
 [Syntax Error] line 0, col 7: Error: Expected known function, got 'DATE_FORMAT'

I've searched some links and find some explain that it should work this way, but for me it looks like im doing something wrong.

http://www.uvd.co.uk/blog/labs/using-mysqls-date_format-in-doctrine-2-0/

Answer 1

DATE_FORMAT along with a bunch of Mysql function are not directly available in Doctrine.

To use them you can either create your own or add an extension like beberlei/DoctrineExtensions and then add the respective function to your doctrine bundle config like

doctrine:
    dbal:
        ....
    orm:
        ....
        dql:
            string_functions:
                DATE_FORMAT: DoctrineExtensions\Query\Mysql\DateFormat

Answer 2

DATE_FORMAT is not a doctrine recognised function. That link that you pasted is custom code the author wrote for him to get date_formate. You can write custom functions in doctrine and register them , neat ey ?

Here is an example of how to do that - http://symfony.com/doc/current/cookbook/doctrine/custom_dql_functions.html

Option 1: You write that custom function for DATE_FORMAT so that you can use it again and again. Option 2: Maybe reformat the date in PHP to match the date format in the database , kinda like

$after7Days->format('Y-m-d');

Answer 3

I am not sure but this is correct for me only:

doctrine:
    orm:
        entity_managers:
            default:
                ...
                dql:
                    datetime_functions:
                        DATE_FORMAT: DoctrineExtensions\Query\Mysql\DateFormat

Answer 4

DATE_FORMAT is not string function, its date_function:

config.yml

doctrine:
     orm:
         dql:
             datetime_functions:
                 date_format: DoctrineExtensions\Query\Mysql\DateFormat