[SOLVED] Cast into a querybuilder

Cast into a querybuilder

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'

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

I'm using Symfony 2.6 and postgreSQL.

Thanks you :)

Solution

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