symfonysymfony2

query with dateformat in where clause in symfony2


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

Solution

  • 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();