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

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 19, 2013 at 16:21

Ahmed Siouani

13.9k12 gold badges63 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Cerad Over a year ago

You are on the right track but this won't work since the Doctrine DQL query processor does not recognize DATE.

Tushar Over a year ago

Hi Ahmed - it's showing the following error 'Error: Expected known function, got 'DATE''

Ahmed Siouani Over a year ago

@Cerad you're right. Didn't seem to be supported. @ Tushar, take a look at Cerad's extension.

Ahmed Siouani Over a year ago

@Tushar I updated my answer based on Cerad's first comment. Take a closer look at the example on how to add and register a Custom DQL function.

vascowhite · Accepted Answer · 2013-03-19 19:08:09Z

1

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.

edited Mar 19, 2013 at 19:08

vascowhite

18.5k9 gold badges64 silver badges79 bronze badges

answered Mar 19, 2013 at 16:31

Cerad

49k9 gold badges92 silver badges94 bronze badges

1 Comment

Tushar Over a year ago

sorry I am new symfony2. where I have to use that extension? My whole code is given bellow...

Lighthart · Accepted Answer · 2013-03-20 17:46:57Z

1

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.

answered Mar 20, 2013 at 17:46

Lighthart

3,6966 gold badges32 silver badges47 bronze badges

Collectives™ on Stack Overflow

Change the date format in doctrine query in symfony2

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related