DATE_FORMAT in query builder

Hi,

I’m having trouble using the Query builder with the MySQL DATE_FORMAT function.

for instance, if I’m building a query like this:

        

        $month_year = new \yii\db\Expression("DATE_FORMAT(`news_date`, '%Y-%m')");

        $months = NewsItem::find()

                    ->select($month_year)

                    ->column();

for some reason, Yii2 quotes the format string, like so:

SELECT DATE_FORMAT(`NewsDate`, `'%Y-%m')` 

note the syntax-breaking backticks around:

'%Y-%m')

has anyone successfully used DATE_FORMAT with Query builder?

I can, and will use straight SQL here, but I thought I’d ask the Yii community about it.

-Charlie

Does the same happen when you use it like this?

->select([$month_year])

Nope - that solves it.

I just realized this is in the documentation:

“Note that if you are selecting an expression like CONCAT(first_name, ’ ', last_name), you should use an array to specify the columns. Otherwise, the expression may be incorrectly split into several parts.”

thanks for your help, and sorry to ask a question I could have figured out myself.

Hi

I used as like below

$query->select(["DATE_FORMAT(created_at, '%d-%m-%Y') as c_date"']) 

and its works fine