Using `DATE()` in Doctrine Querybuilder

Question

I need to get all rows where DATE(a.when) matches the string 2014-09-30.

$builder = $this->em->createQueryBuilder(); $builder->select('a') ->from('Entity\Appointment', 'a') ->andWhere('a.when = :date') ->setParameter('date', $date);

a.when is a full DATETIME; :date is only a string (in DATE format).

The following and variations didn't work:

 ->andWhere('DATE(a.when) = :date') Error: Expected known function, got 'DATE'

What's the correct usage here?

possible duplicate of Doctrine 2 - Filter results by a datetime field's date part — andy
– andy, Commented Sep 23, 2014 at 15:56
@andy not a duplicate. I'm using the queryBuilder and the error message using createQuery and the queryBuilder is the same: Error: Expected known function, got 'DATE'. — Daniel W.
– Daniel W., Commented Sep 23, 2014 at 16:02
The erro is the same because there is no DATE() in DQL. If you use the WHERE clause from the linked duplicate, your approach should work as well. — andy
– andy, Commented Sep 23, 2014 at 16:05

Arkadiusz 'flies' Rzadkowolski · Accepted Answer · 2019-01-17 07:47:27Z

15

Thanks to andy, using this now:

$builder = $this->em->createQueryBuilder(); $builder->select('a') ->from('Entity\Appointment', 'a') ->andWhere('a.when >= :date_start') ->andWhere('a.when <= :date_end') ->setParameter('date_start', $date->format('Y-m-d 00:00:00')) ->setParameter('date_end', $date->format('Y-m-d 23:59:59'));

edited Jan 17, 2019 at 7:47

Arkadiusz 'flies' Rzadkowolski

5,8372 gold badges32 silver badges31 bronze badges

answered Sep 23, 2014 at 16:28

Daniel W.

32.6k15 gold badges99 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Arkadiusz 'flies' Rzadkowolski Over a year ago

Answer is incorrect. Both a.when where caluses should use closed intervals (>= & <=). Using code above - rows with 23:59:59 and 00:00:00 will NEVER get selected.

Daniel W. Over a year ago

@Arkadiusz'flies'Rzadkowolski so you are downvoting because I made < instead of <= ? This minor detail is not affecting the implementation itself which was perfectly legit in 2014 and is still in 2019.

Arkadiusz 'flies' Rzadkowolski Over a year ago

I have edited your answer & removed downvote. But I disagree it's minor detail. Closed / Open intervals make a big deal in real life :)

Jack B Over a year ago

This should be < with $date->modify('+1 day')->format('Y-m-d 00:00:00') as the database may or may not consider microseconds which could mean a date_end on 23:59:59:200 is not considered.

Muc · Accepted Answer · 2017-03-15 20:52:47Z

This actually is a very common question. It turns out that not all sql databases support a DATE function, so the good people in charge of Doctrine decided not to support it nativelly.

Kind of wish they did because it would have saved a bunch of people a fair amount of effort.

So add this rather magical class to your project:

namespace Cerad\Bundle\CoreBundle\Doctrine\DQL; use Doctrine\ORM\Query\Lexer; use Doctrine\ORM\Query\AST\Functions\FunctionNode; class Date extends FunctionNode { public $date; public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) { return "DATE(" . $sqlWalker->walkArithmeticPrimary($this->date) . ")"; } public function parse(\Doctrine\ORM\Query\Parser $parser) { $parser->match(Lexer::T_IDENTIFIER); $parser->match(Lexer::T_OPEN_PARENTHESIS); $this->date = $parser->ArithmeticPrimary(); $parser->match(Lexer::T_CLOSE_PARENTHESIS); } }

Then wire it up in the doctrine section of your app/config.yml:

doctrine: orm: default_entity_manager: default auto_generate_proxy_classes: %kernel.debug% entity_managers: default: connection: default ... dql: datetime_functions: date: Cerad\Bundle\CoreBundle\Doctrine\DQL\Date

http://doctrine-orm.readthedocs.org/en/latest/cookbook/dql-user-defined-functions.html http://symfony.com/doc/current/cookbook/doctrine/custom_dql_functions.html http://symfony.com/doc/current/reference/configuration/doctrine.html

There are other bundles out there with more sql functions. Oddly enough, the first time I looked a few years ago, none of them had Date defined. So I just made my own.

====================================================================

Update 01

I did not check the tags carefully and assumed that this was a Symfony 2 application. The Date class stays the same. You wire it up by getting the doctrine configuration object.

$config = new \Doctrine\ORM\Configuration(); $config->addCustomDatetimeFunction('DATE', 'blahblah\Date');

Check the Doctrine link for details.

I give you an upvote for your effort, unfortunately I cannot change our core libs like Doctrine. I am going with a non-DQL way now until I find a better way.
You are not changing the core library. You are just adding a custom function. Nothing inside of Doctrine is being changed.
I can't get it "wired up". I don't have this config file. I tried with YEAR(), DAY() and MONTH() now because their class files were already there but the error is accordingly the same..
See my update. Just add it to your Doctrine configuration object.
Ah ok this time it's working, we got Doctrine hooked into ZF1.

Leandro Neves · Accepted Answer · 2020-10-02 15:13:05Z

A different approach using $qb->expr()->between() in the same andWhere:

 $builder = $this->em->createQueryBuilder(); $builder->select('a') ->from('Entity\Appointment', 'a') ->andWhere($qb->expr()->between('a.when', ':date_start', ':date_end')) ->setParameter('date_start', $date->format('Y-m-d 00:00:00')) ->setParameter('date_end', $date->format('Y-m-d 23:59:59'));

can you add a bit of an explanation to the code you are sharing, what you are doing, and how it is a different approach ?

Collectives™ on Stack Overflow

Using `DATE()` in Doctrine Querybuilder

3 Answers 3

4 Comments

7 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

7 Comments

1 Comment

Linked

Related