How to use DATE () in Doctrine 2 DQL?

Question

In symfony, when I used the following query with the DATE function in mysql, I get an error

    SELECT employer.companyName AS employerName, jobs.jobId, jobs.industryId, 
jobs.focusId, jobs.companyName, jobs.employerId, jobs.jobTitle, DATE(jobs.createdDate) AS 
createdDate , DATE(jobs.endDate) AS endDate , jobs.replyTo, jobs.featured , jobs.jobType, 
jobs.status  FROM Acme\\AppsBundle\\Entity\\Jobs jobs , Acme\\AppsBundle\\Entity\\Employer 
employer  WHERE  jobs.employerId = employer.employerId  GROUP BY  jobs.jobId  ORDER BY 
jobs.jobId DESC 

Why is this and what a workaround exists to overcome this situation, in the database these fields ie end_date are stored as mysql type 'date'

    [2014-09-17 05:52:42] request.CRITICAL: Uncaught PHP Exception Doctrine\ORM\Query
\QueryException: "[Syntax Error] line 0, col 138: Error: Expected known function, got 
'DATE'" at /.../Doctrine/ORM/Query/QueryException.php line 52 {"exception":"[object] 
(Doctrine\\ORM\\Query\\QueryException: [Syntax Error] line 0, col 138: Error: Expected known function, got 'DATE' at /var/w.../doctrine/orm/lib/Doctrine/ORM/Query
/QueryException.php:52, Doctrine\\ORM\\Query\\QueryException: SELECT employer.companyName 
AS employerName, jobs.jobId, jobs.industryId, jobs.focusId, jobs.companyName, jobs.employerId, jobs.jobTitle, DATE(jobs.createdDate) AS createdDate , DATE(jobs.endDate) 
AS endDate , jobs.replyTo, jobs.featured , jobs.jobType, jobs.status  FROM Acme\\AppsBundle
\\Entity\\Jobs jobs , Acme\\AppsBundle\\Entity\\Employer employer  WHERE  jobs.employerId 
= employer.employerId  GROUP BY  jobs.jobId  ORDER BY  jobs.jobId DESC  at /var/w...
/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:41)"} []

Answer 1

DQLknows only a few standard sql functions (e.g. coalesce). To be able to use your custom function, you need to register it and tell doctrinehow to translate it into raw sql. Follow these guides:

Symfony doc

Doctrine doc

Answer 2

, https://github.com/beberlei/DoctrineExtensions.

,

doctrine:
    orm:
        dql:
            string_functions:
                DATE: DoctrineExtensions\Query\Mysql\Date

DQL ( ),

DATE(jobs.endDate) AS endDate