manix manix - 2 years ago 113
PHP Question

Select entries between dates in doctrine 2

I will go insane with this minimal error that I'm not getting fix. I want to select entries between two days, the examples below ilustrate all my fails:

opt 1.

$qb->where('e.fecha > ' . $monday->format('Y-m-d'));
$qb->andWhere('e.fecha < ' . $sunday->format('Y-m-d'));


result (0 entries):

SELECT r0_.id_reservacion AS id_reservacion0, r0_.fecha AS fecha1, r0_.cliente AS cliente2
FROM reservacion r0_
WHERE (r0_.fecha > 2012 - 07 - 16) AND (r0_.fecha < 2012 - 07 - 22)


opt 2

$qb->add('where', 'e.fecha between 2012-01-01 and 2012-10-10');


result (0 entries):

SELECT r0_.id_reservacion AS id_reservacion0, r0_.fecha AS fecha1, r0_.cliente AS cliente2
FROM reservacion r0_ WHERE r0_.fecha
BETWEEN 2012 - 01 - 01 AND 2012 - 10 - 10


This is my table with current entries:

id fecha cliente
1 2012-07-16 00:00:00 2
2 2012-07-16 13:00:00 4
3 2012-07-22 23:00:00 4


Edit 1



In order to evaluate the sql to avoid doubts, I ran this query:

$qb->where('e.fecha > ' . $sunday->format('Y-m-d'));


result (3 entries):

SELECT r0_.id_reservacion AS id_reservacion0, r0_.fecha AS fecha1, r0_.cliente AS cliente2


So, looks like the sql is not the problem.
FROM reservacion r0_
WHERE r0_.fecha > 2012 - 07

Answer Source

You can do either…

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

or…

$qb->where('e.fecha > :monday')
   ->andWhere('e.fecha < :sunday')
   ->setParameter('monday', $monday->format('Y-m-d'))
   ->setParameter('sunday', $sunday->format('Y-m-d'));
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download