when I run a query with date in where clause, following error is showed...
[Syntax Error] line 0, col 129: Error: Expected known function, got 'DATE_FORMAT'
the query is given below
$query = $this->getEntityManager()->createQuery(
"SELECT a.id, a.amont, a.paymentDescrip, a.paymentType, a.paymentDate
FROM RegalSmsBundle:DailyTransaction a
WHERE DATE_FORMAT(a.paymentDate,'%Y-%m-%d') = :paymentDate
and a.students = :studentId"
)->setParameter('studentId', $studentId)
->setParameter('paymentDate','2013-03-11');
return $query->getResult();
Doctrine doesn't have DATE_FORMAT
function defined by default. It's possible to Register Custom DQL Function.
But you can compare date easily (assuming a.paymentDate
is of type date
):
$query = $this->getEntityManager()->createQuery("
SELECT a.id, a.amont, a.paymentDescrip, a.paymentType, a.paymentDate
FROM RegalSmsBundle:DailyTransaction a
WHERE a.paymentDate = :paymentDate AND a.students = :studentId
")
->setParameter('studentId', $studentId)
->setParameter('paymentDate', new \DateTime('2013-03-11'))
;
return $query->getResult();
Edit: I prefer using querybuider to writing DQL. It would look like this:
$qb = $this->getEntityManager()->getRepository('RegalSmsBundle:DailyTransaction')->createQueryBuilder('a');
$qb
->select('a') // select whole entity
->where($qb->expr()->andX(
$qb->expr()->eq('a.paymentDate', ':paymentDate')
$qb->expr()->eq('a.students', ':studentId')
))
->setParameter('studentId', $studentId)
->setParameter('paymentDate', new \DateTime('2013-03-11'))
;
return $qb->getQuery()->getResult();