Doctrine Query: How to Query with Date Range?

Question

Basically, I want to query using a date range as follows:

$qb = $this->createQueryBuilder("c");

if ($createDateStart) {
        $createDateStart = $createDateStart->format('d-M-Y');
        $qb->where("c.createDate >= :createDateStart")->setParameter(
                "createDateStart", $createDateStart);
}

if ($createDateStart && $createDateEnd) {
        $createDateEnd = $createDateEnd->format('d-M-Y');
        $qb->andWhere("c.createDate <= :createDateEnd")->setParameter(
                "createDateEnd", $createDateEnd);
} else 
        if ($createDateEnd) {
            $createDateEnd = $createDateEnd->format('d-M-Y');
            $qb->where("c.createDate <= :createDateEnd")->setParameter(
                    "createDateEnd", $createDateEnd);
        }

and here is the entity for 'createDate':

/**
 * @Column(type="datetime")
 */
private $createDate;

However, when I test using this date range: $createDateStart = 01-Jan-2014 to $createDateEnd = 31-Jan-2014. The entries from last year i.e. 2013 is displayed.

I'm guessing that I have to format $createDate as well (d-M-Y) but how do I do that from the query statement?

I'd appreciate your help.

You shouldn't need to convert your datetime object to a string before using it as a parameter, are your entity properties defined as datetime type? — calumbrodie, Jan 22 '14 at 22:51
Yes. They are. But what is the cause of the issue above? Is the syntax of the comparison correct? — EngineerCoders, Jan 22 '14 at 23:39

score 2 · Answer 1 · edited May 23 '17 at 12:21

2

$qb->where('e.fecha BETWEEN :monday AND :sunday')
   ->setParameter('monday', $monday->format('Y-m-d'))
   ->setParameter('sunday', $sunday->format('Y-m-d'));

Select entries between dates in doctrine 2

it solved.

edited May 23 '17 at 12:21

Community

1
1

answered Mar 19 '14 at 15:21

Amin Arab

530
4
19

Doctrine Query: How to Query with Date Range?

1 Answers1