Skip to content
Advertisement

How to add DATE to doctrine query?

I have a date field in Y-m-d H:i:s format. Now user can search by using date only. I have query like this:

$qb->andWhere('DATE(vc.updatedDatetime) >= :startDate AND DATE(vc.updatedDatetime) <= :endDate')
->setParameter('startDate', $filterArray['startDate'])
->setParameter('endDate', $filterArray['endDate']);

I have added following lines at my doctrine.yaml file.

    dql:
        string_functions:
            DATE: DoctrineExtensionsQueryMysqlDate

But I am getting following error:

Attempted to load class "Date" from namespace "DoctrineExtensionsQueryMysql".
Did you forget a "use" statement for e.g. "Twig_Extensions_Extension_Date" or "SymfonyComponentValidatorConstraintsDate"?

Advertisement

Answer

While you could create your own extension to add the DATE function to Doctrine, or download something like beberlei/DoctrineExtensions to do it for you, in the end is a waste of time. You do not need it.

You could simply do:

$qb
    ->andWhere('vc.updatedDatetime > :startDate')
    ->andWhere('vc.updatedDatetime < :endDate')
    ->setParameter('startDate', $startDate)
    ->setParameter('endDate', $endDate)

For your start date, you need nothing but to use the > operator. A date time string without a time part will be treated as a datetime with the time part set to 00:00:00.

For the end date, you simply need to use the < operator, and add one day to whatever date the user provides.

E.g.

$endDate = (new DateTime($filterArray['endDate']))->add(new DateInterval('P1D'))

Assuming the provided date is in “YYYY-MM-DD” format, which seems to be the case because of how you are using your code, the above would suffice.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement