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?

Kinnikinnick

possible duplicate of Doctrine 2 - Filter results by a datetime field's date part – Sanitary

@Sanitary 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'. – Kinnikinnick

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. – Sanitary

Answer 1

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.

Gynecium

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. – Kinnikinnick

You are not changing the core library. You are just adding a custom function. Nothing inside of Doctrine is being changed. – Gynecium

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.. – Kinnikinnick

See my update. Just add it to your Doctrine configuration object. – Gynecium

Ah ok this time it's working, we got Doctrine hooked into ZF1. – Kinnikinnick

I've come here because I've implemented DATE() pretty much identically to you but it's returning a string rather than a DateTime... is there some configuration option we have missed? – Cockboat

@AndyPreston Always fun to see old threads still active. This thread is about using the mysql DATE function in a DQL query. Nothing to do with what is ultimately returned. Check your entity mappings. Try to simplify your query as much as possible. If you still have issues then consider opening a new question. – Gynecium

Answer 2

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

Kinnikinnick

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. – Margaritamargarite

@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. – Kinnikinnick

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

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. – Raquelraquela

Answer 3

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

Backwash

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 ? – Equality