Symfony 2.4.1 and Doctrine 2 dates interval query

Question

I have two tables: Empleado and Fichaje in a (1..*) relationship.
I created a query builder for getting fichajes corresponding to IdEmpleado (key) property in Fichaje.
I attempt to filter those Fichajes, but it never works.
So I've searched for any clear example of dates in Doctrine for this basic case in vane.
The query result is empty always. No error is thrown.
If I check for IdEmpleado parameter only it gives me all the available records. The dates interval is the problematic one.

Note: I checked this similar post

Here is the table data, I'm quite convinced of the dates availability.

query and table results

This is my function:

public function empleadoAction(Request $request){

     ...
        $repository = $em->getRepository('ZkTimeBundle:Fichaje');
        $fichajes = $repository->FindByEmpleadoAndDateInterval(
            array(
                'IdEmpleado' => $workerId,
                'FechaInicial' => (new \DateTime('2014-01-10'))->format('Y-m-d'),
                'FechaFinal' => (new \DateTime('today'))->format('Y-m-d')
            ));
     ...

This is my repository function:

public function FindByEmpleadoAndDateInterval($parameters = array(), $limit = null){

...
    $qb = $this->createQueryBuilder('q');
    $qb
        ->where('q.IdEmpleado = :IdEmpleado')
        ->andWhere('q.Fecha > :FechaInicial')
        ->andWhere('q.Fecha < :FechaFinal')
        ->setParameter('IdEmpleado', $parameters['IdEmpleado'])
        ->setParameter('FechaInicial', $parameters['FechaInicial'])
        ->setParameter('FechaFinal', $parameters['FechaFinal'])
    ;

    return $qb->getQuery()->execute();
}

score 1 · Answer 1 · answered Feb 14 '14 at 13:17

Folks, careful with this, have a nice look to the format of dates when you're working with Doctrine 2. The problem was this:

-I've set dates format as: 'Y-M-d', but: 'Ymd' was the correct one (in my particular case). So, have faith in Doctrine 2 and try every known format (Y-m-d), (Y/m/d), etc. So, you could use dates intervals in these simple ways:

 public function findByEmpleadoAndDateInterval($parameters = array(), $limit = null)
    {
        $qb = $this->createQueryBuilder('q');
        $qb->where('q.IdEmpleado = :IdEmpleado')
            ->andWhere('q.Fecha between :FechaInicial and :FechaFinal')
            ->setParameters($parameters);

        return $qb->getQuery()->execute();
    }

OR

public function findByEmpleadoAndDateInterval($parameters = array(), $limit = null)
    {
        $qb = $this->createQueryBuilder('q');
        $qb->where('q.IdEmpleado = :IdEmpleado')
            ->andWhere('q.Fecha >= :FechaInicial and q.Fecha <= :FechaFinal')
            ->setParameters($parameters);

        return $qb->getQuery()->execute();
    }

Maybe, out there, there are more elaborated examples, but this case took me a while to figure it out. Specially because isn't directly accesible online.

Symfony 2.4.1 and Doctrine 2 dates interval query

Here is the table data, I'm quite convinced of the dates availability.

This is my function:

This is my repository function:

1 Answers1