CODEWITHSUNDEEP
mysqlsymfonydoctrine
Programmer
Programmer

Reputation: 157

Retreive date from MySQL DATETIME in Doctrine QueryBuilder

I have a MySQL date stored in DATETIME format. So I would like to know how to use date() in my Doctrine QueryBuilder's where clause. For example, 2013-02-01 12:51:17 is the date in MySQL. But I need to retrieve only the date. This is what I have tried: 

$qb = $this->getEntityManager()->createQueryBuilder()
    ->select('t.balance','a.id','t.date')
    ->from('TestMainBundle:Transaction','t')               
    ->groupBy('a.id')
    ->orderBy('a.id')
    ->where("t.date in date('t.date') "); 

return $qb->getQuery()->getResult();

I received the following error:

QueryException: [Syntax Error]: Error: Expected Doctrine\ORM\Query\Lexer::T_OPEN_PARENTHESIS, got 'date'

Upvotes: 1

Views: 2750

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157838

As it is pointed out in the comments, you cannot use mysql-specific date() or date_format() functions in Doctrine.

But for the particular case of searching a certain date in the datetime field, you can treat a date like a string, and thus use LIKE operator

->Where('t.date LIKE :date')
->setParameter(':date', "$date%")

As of

But I need to retreive only the date

you just format the returned value using format("Y-m-d") method. i.e.

echo $row->getDate()->format("Y-m-d");

Upvotes: 1

Barkati.med
Barkati.med

Reputation: 630

Hi You can use SUBSTRING to fix your probleme

->where('SUBSTRING(t.date, 1, 10) IN (:param)')
->setParameter('param', array('2017-04-06'))

Upvotes: 1

Hugo Soltys
Hugo Soltys

Reputation: 247

You don't need the "date" in your where clause.

Juste remove it like this :

->where('t.date in (:yourwanteddate)')
->setParameter('yourwanteddate', '2013-02-01 12:51:17');

Upvotes: -1

Related Questions