Problem with CDbExpression

Yii 1.1 General Discussion
1

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.

2

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.

3

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.