Select entries between dates in doctrine 2

Question

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- gt where  e fecha  gt       monday- gt format  Y-m-d      qb- gt andWhere  e fecha  lt       sunday- gt 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  gt  2012 - 07 - 16  AND  r0  fecha  lt  2012 - 07 - 22    opt 2   qb- gt 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- gt where  e fecha  gt       sunday- gt 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

User · Accepted Answer

You can do either      qb- gt where  e fecha BETWEEN  monday AND  sunday      - gt setParameter  monday    monday- gt format  Y-m-d       - gt setParameter  sunday    sunday- gt format  Y-m-d       or      qb- gt where  e fecha  gt   monday      - gt andWhere  e fecha  lt   sunday      - gt setParameter  monday    monday- gt format  Y-m-d       - gt setParameter  sunday    sunday- gt format  Y-m-d

User · Answer

I believe the correct way of doing it would be to use query builder expressions    now   new DateTimeImmutable     thirtyDaysAgo    now- gt sub new  DateInterval  P30D      qb- gt select  e      - gt from  Entity   e      - gt add  where    qb- gt expr  - gt between               e datefield                 from                 to                     - gt setParameters array  from    gt   thirtyDaysAgo   to    gt   now      http   docs doctrine-project org en latest reference query-builder html the-expr-class  Edit  The advantage this method has over any of the other answers here is that it s database software independent - you should let Doctrine handle the date type as it has an abstraction layer for dealing with this sort of thing   If you do something like adding a string variable in the form  Y-m-d  it will break when it goes to a database platform other than MySQL  for example

User · Answer

EDIT  See the other answers for better solutions      The original newbie approaches that I offered were  opt1     qb- gt where  e fecha  gt        monday- gt format  Y-m-d            qb- gt andWhere  e fecha  lt        sunday- gt format  Y-m-d             And  opt2     qb- gt add  where    e fecha between  2012-01-01  and  2012-10-10       That was quick and easy and got the original poster going immediately   Hence the accepted answer    As per comments  it is the wrong answer  but it s an easy mistake to make  so I m leaving it here as a  what not to do

User · Answer

Look how I format my date  jour in the parameters  It depends if you use a expr  - like or a expr  - lte   qb         - gt select  e           - gt from  LdbPlanningBundle EventEntity    e           - gt where               qb- gt expr  - gt andX                   qb- gt expr  - gt orX                       qb- gt expr  - gt like  e start     jour1                         qb- gt expr  - gt like  e end     jour1                         qb- gt expr  - gt andX                           qb- gt expr  - gt lte  e start     jour2                             qb- gt expr  - gt gte  e end     jour2                                                             qb- gt expr  - gt eq  e user     user                                   - gt andWhere  e user    user            - gt setParameter  user    user          - gt setParameter  jour1        jour- gt format  Y-m-d                - gt setParameter  jour2    jour- gt format  Y-m-d            - gt getQuery           - gt getArrayResult

[php] Select entries between dates in doctrine 2

Examples related to php

Examples related to date

Examples related to doctrine-orm

Examples related to between

Examples related to query-builder