CODEWITHSUNDEEP
mysqlsymfonydatetimedoctrinesymfony-2.3
Cristhian Boujon
Cristhian Boujon

Reputation: 4190

How to compare date and datetime with doctrine

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.

Upvotes: 2

Views: 1625

Answers (2)

cretthie
cretthie

Reputation: 349

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)')

beberlei/DoctrineExtensions

Upvotes: 0

Maksym Moskvychev
Maksym Moskvychev

Reputation: 1674

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

Upvotes: 1

Related Questions