Problem with CDbExpression

Hi,

I’m facing this issue. I want to use a d/m/Y formatted date and insert it in a MySQL database date column. So I use CDbExpression like this:

$model->birthdate = new CDbExpression("STR_TO_DATE(:date, '%d/%m/%Y')", [':date' => $model->fmtBirthDate]);

As I wrote, $model->fmtBirthDate has the format dd/mm/yyyy (like ‘15/05/2017’). However, the previous line fails with database error:

CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: 'STR_TO_DATE(:date, '%d/%m/%Y')' for column 'birthdate' at row 1. The SQL statement executed was: UPDATE `t_member` SET `id`=:yp0, `birthdate`=:yp1 WHERE `t_member`.`id`=69121. Bound with :yp0=69121, :yp1='STR_TO_DATE(:date, \'%d/%m/%Y\')'

What is wrong here?Tried it with 1.1.17 and 1.1.18 and still the same error.

Of course

select str_to_date('15/05/2017', '%d/%m/%Y');

works just fine in a mysql prompt.

Any help would be greatly appreciated.

What is the type of date field in your database?

If field has type ‘timestamp’ you should insert value using this format ‘Y-m-d H:i:s’.

For pages/views you can use format any you want.

The column datatype in the database is of type date, so no timestamp is needed (even in that case, the default time of 00:00:00 would be inserted). The problem is while this syntax works in mysql prompt, this does not work inside CDbExpression.