Current year with Doctrine

Question

I tried to have the current year in a query builder in a where condition with Doctrine 2 but impossible. With My SQL it's ok but with Doctrine nothing to do, it don't works. So could someone help me about this?

I tried (One by one):

->where('year = CURRENT_TIMESTAMP()')

->where('SUBSTRING(i.interventionDate),1,4) = CURRENT_DATE()')

->where('Year(i.interventionDate) = YEAR(CURDATE()')

->where($qb->expr()->gt('i.interventionDate', 'CURRENT_TIMESTAMP()'))

->where('SUBSTRING(i.interventionDate), 1,4) =  CURRENT_TIMESTAMP()')

Thanks for advance

What have you tried so far? Can you post your code? – Taylan Oct 19 '18 at 05:47 — Taylan, Oct 19 '18 at 05:47
Yes, I put the differents codes in the first post. – delph49 Oct 19 '18 at 05:54 — delph49, Oct 19 '18 at 05:54

dbrumann · Accepted Answer · 2018-10-19T07:03:50.163

Some of MySQL's Date functionality is platform specific and Doctrine is an abstraction over multiple platforms. That is why they don't support for example the YEAR() function out of the box. You can write your custom logic or use the beberlei/DoctrineExtensions to add these and other mysql-specific capabilities to your setup. Install the bundle and then in your doctrine configuration in the config.yml add the new dql types by mapping the function name to the respective class like this:

doctrine:
    orm:
        dql:
            datetime_functions:
                date: DoctrineExtensions\Query\Mysql\Date
                ...

or use the config file from the library.

Afterwards you can write the query to convert both the current date and the date in your table to a year and compare them, like this:

->where('YEAR(i.interventionDate) = YEAR(CURRENT_DATE())');

`where('YEAR(i.interventionDate) = YEAR(CURRENT_DATE())')` does not work? — dbrumann, Oct 19 '18 at 06:55

Current year with Doctrine

1 Answers1