Change the date format in doctrine query in symfony2

Question

In table column (a.paymentDate) date is inserted in Y-m-d H:m:i format. I want to query all the entries in particular date. For that I have to change the date format from Y-m-d H:m:i to Y-m-d. My query is given bellow.

namespace Regal\SmsBundle\Repository;

use Doctrine\ORM\EntityRepository;


/**
 * DailyTransactionRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class DailyTransactionRepository extends EntityRepository
{
    public function getDailyPayment($studentId,$paymentDate)
    {
        $paymentDate= new \DateTime('2013-03-11');
     $query = $this->getEntityManager()->createQuery("
        SELECT a.id, a.amont, a.paymentDescrip, a.paymentType, a.paymentDate
        FROM RegalSmsBundle:DailyTransaction a 
        WHERE DATE(a.paymentDate) = :paymentDate AND a.students = :studentId
    ")
    ->setParameter('studentId', $studentId)
    ->setParameter('paymentDate', $paymentDate->format('Y-m-d'))
;

return $query->getResult();
    }
}

Solution 1

Try this,

$query = $this->getEntityManager()->createQuery("
        SELECT a.id, a.amont, a.paymentDescrip, a.paymentType, a.paymentDate
        FROM RegalSmsBundle:DailyTransaction a 
        WHERE DATE(a.paymentDate) = :paymentDate AND a.students = :studentId
    ")
;

$compareTo = new \DateTime('2013-03-11');
$query->setParameters(array(
        'studentId'   => $studentId, 
        'PaymentData' => $compareTo->format('Y-m-d')),
));
    
return $query->getResult();

Update,

It seems like Date() is not recognized. So, to let the code snippet I shared work. You should add a Custom DQL Function and Register the extension to let your Entity Manager know about it.

Take a look at the documentation, it's well explained.

Also,

I think it's possible to use DATE_DIFF(date1, date2) for comparaison - It gives you the difference in days, which do fit your need.

Solution 2

To get Ahmed's answer to work (assuming you are using Mysql) you will need to add a doctrine extension.

namespace Cerad\Bundle\GameBundle\Doctrine\DQL;

use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;

/**
* "DATE" "(" SimpleArithmeticExpression ")". Modified from DoctrineExtensions\Query\Mysql\Year
*
* @category DoctrineExtensions
* @package DoctrineExtensions\Query\Mysql
* @author Rafael Kassner <[email protected]>
* @author Sarjono Mukti Aji <[email protected]>
* @license MIT License
*/
class Date extends FunctionNode
{
    public $date;

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return "DATE(" . $sqlWalker->walkArithmeticPrimary($this->date) . ")";
    }
    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->date = $parser->ArithmeticPrimary();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

And then look in the cookbook to see how you can activate the extension by adding a dql section to your entity manager configuration file.

Solution 3

A lower tech approach using query builder might be:

class DailyTransactionRepository extends EntityRepository
{
    public function getDailyPayment($studentId,$paymentDate)
    {
        $paymentDate= new \DateTime('2013-03-11');        
        $nextDate = paymentDate;
        $nextDate->modify('+1 day');

        $qb = $this->getEntityManager()->createQueryBuilder( 'a' );
        $qb->andWhere("a.paymentDate > '$paymentDate'");
        $qb->andWhere("a.paymentDate < '$nextDate'");

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

You don't really need to select the fields explicitly unless you absolutely must have a partial object. All of the fields will be available by entity getters and setters.