Cast into a querybuilder

Question

I have a querybuilder like this:

        ->andWhere('i.createdAt LIKE :date')
        ->setParameter('date', $date.'%')

The field created_at is a DateTime and my $date is a string. Is there any way to cast my created_at as a string ?

I've already tried :

            ->andWhere('i.createdAt::text LIKE :date')

Console :

.... has no field or association named createdAt::text

Or :

            ->andWhere('to_char(i.createdAt) LIKE :date')

Console :

  [Syntax Error] line 0, col 208: Error: Expected known function, got 'to_char'

Or

  [Syntax Error] line 0, col 208: Error: Expected known function, got 'cast'

I'm using Symfony 2.6 and postgreSQL.

Thanks you :)

Answer 1

format the date to a string like so:

$date->format('Y-m-d H:i:s')

Or leave out the day and time since it's a LIKE query

$date->format('Y-m-') . '%'

Consider however querying with dates between a start and an end point:

$nextMonth = clone $date;
$nextMonth->modify('+1 month');

Then you can do a greater or equal to first date and less than or equal to the end date

Answer 2

Thanks you, that's a great idea.

    $startInterval = \DateTime::createFromFormat('Y-m', $date);
    $endInterval = clone $startInterval;
    $endInterval->modify('+1 month'); 

This is a way better !

        ->andWhere('i.createdAt > :startInterval')
        ->andWhere('i.createdAt < :endInterval')
        ->setParameter('startInterval', $startInterval)
        ->setParameter('endInterval', $endInterval)