Search code examples
symfonydql

YEAR(CURDATE() in DQL (Doctrine Query Language)

In MySQL there is YEAR(CURDATE(). 

mysql> SELECT YEAR(CURDATE());
+-----------------+
| YEAR(CURDATE()) |
+-----------------+
|            2017 |
+-----------------+

How to obtain it in DQL? In directly usage

$er->createQueryBuilder('s')
   ->where('s.year = YEAR(CURDATE()');

I obtaining:

[Syntax Error] line 0, col 105: Error: Expected known function, got 'YEAR'

I trying 

SUBSTRING(CURDATE(),1,4);

that again works correctly in pure MySQL, but in DQL I have now error

[Syntax Error] line 0, col 115: Error: Expected known function, got 'CURDATE'

Similar results give:

SUBSTRING(NOW(),1,4);

I mean it works in MySQL, but in doctrine end with exception:

[Syntax Error] line 0, col 115: Error: Expected known function, got 'NOW'
Solution

  • To do this you would have to add some extra functions to doctrine.

    Here is the link for some that would give you what you need. https://github.com/luxifer/doctrine-functions.

    However you would probably best using a php DateTime Object or date() to get the current year as pass that in as a parameter e.g.

    $er->createQueryBuilder('s')
->where('s.year', date('Y'));