query with dateformat in where clause in symfony2

Question

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

tomas.pecserke · Accepted Answer · 2013-03-19T16:51:57.190

0

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

edited Mar 19 '13 at 16:51

answered Mar 18 '13 at 17:54

tomas.pecserke

3,260
25
26

Thanks for the answer. but in my database a.pymentDate format is date(Y-m-d h:m:s ). how it compares with DateTime('2013-03-11') format – Tushar Mar 19 '13 at 00:20
Doctrine translates `\DateTime` to database date type when executing DQL query. – tomas.pecserke Mar 19 '13 at 16:44
in table there is one entry where paymentDate is '2013-03-11 16:37:01' . wehen ->setParameter('paymentDate', new \DateTime('2013-03-11 16:37:01')) its work properly but ->setParameter('paymentDate', new \DateTime('2013-03-11'))..query doesn't get any record – Tushar Mar 19 '13 at 17:29
It seems you use `datetime` as a field type, not `date` - if time doesn't matter in your case, consider changing field type. Otherwise there are `YEAR`, `MONTH`, and `DAY` DQL functions available. – tomas.pecserke Mar 19 '13 at 22:25

query with dateformat in where clause in symfony2

1 Answers1