Symfony3 Doctrine findByDate filter by month and/or year

Question

I need to query Statistics by month and/or year but I get this error:

Error: Expected known function, got 'YEAR'

My code:

public function findByMonthYear($month, $year)
{
    $qb = $this->createQueryBuilder('p')
        ->where('YEAR(p.date) = :year')
        ->andWhere('MONTH(p.date) = :month');

    $qb->setParameter('year', $year)
        ->setParameter('month', $month);

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

With some research it shows that dql doesn't have YEAR() function so it's fare...http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/working-with-datetime.html

---

In the end, I'm not stick on the solution. So if you've got a clean one for the same result I'll take it as an answer.

A colleague told me that a solution as the following one may works:

where('p.date = :date')
setParameter('date', ''.$year.'-'.$month.'-%') // 2016-07-%

No success so far.

Answer 1

public function findByMonthYear($month, $year)
{
    $fromTime = new \DateTime($year . '-' . $month . '-01');
    $toTime = new \DateTime($fromTime->format('Y-m-d') . ' first day of next month');
    $qb = $this->createQueryBuilder('p')
        ->where('p.date >= :fromTime')
        ->andWhere('p.date < :toTime');
        ->setParameter('fromTime', $fromTime)
        ->setParameter('toTime', $toTime);
    return $qb->getQuery()->getOneOrNullResult();
}