I need to get all Ad instances where their dates are equals to a specific date. Problem is that the ad.date field is a datetime so how do I compare two dates?
I have tried this:
$result = $query_builder ->select("ad") ->where("DATE(ad.date) = :date") ->setParameter("date", $some_date) ->getQuery() ->getResult(); But a exception was threw:
[Syntax Error] line 0, col 50: Error: Expected known function, got 'DATE' I have also thought to create a virtual property but it didn't work.
In your case doctrine compains that "DATE()" is unknown function. You need to tell to Doctrine that this function is OK. This question was discussed here.
Basically you need to use DoctrineExtensions. To install it use command:
composer require beberlei/DoctrineExtensions And then add to your config.yml:
doctrine: orm: dql: string_functions: DATE: DoctrineExtensions\Query\Mysql\Date If you want to compare only the day, or only the the month, or ... you can add this in your config.yml file :
orm: auto_generate_proxy_classes: '%kernel.debug%' naming_strategy: doctrine.orm.naming_strategy.underscore auto_mapping: true dql: datetime_functions: DAY: DoctrineExtensions\Query\Mysql\Day MONTH: DoctrineExtensions\Query\Mysql\Month YEAR: DoctrineExtensions\Query\Mysql\Year DATE: DoctrineExtensions\Query\Mysql\Date HOUR: DoctrineExtensions\Query\Mysql\Hour MINUTE: DoctrineExtensions\Query\Mysql\Minute SECOND: DoctrineExtensions\Query\Mysql\Second then you can use it like that :
->where('YEAR(associationname.thedatefield) > YEAR(:anotherdate)') 