How to make search filters work with formatted dates in Yii2?

Question

$query->andFilterWhere(['like', 'date_of_birth', date('d-m-Y',strtotime($this->date_of_birth))]);

It's not working..

Answer 1

¿Are you using MySQL? then the date format is: YYYY-MM-DD. This is how MySQL works and you cannot change that.

You can show the date in the format you want on your website and your forms, but you have to pass it to MYSQL as MYSQL expects to receive it. So what you should do is the following:

In your model class:

public function beforeSave($insert) {

    /* for example if my form date format is: dd-mm-yyyy 
    You must change it for yyyy-mm-dd before saving into DDBB */

    if (!empty($this->date_of_birth )) {
        $this->date_of_birth = Yii::$app->formatter->asDate($this->date_of_birth, 'php:Y-m-d');
    }

    return parent::beforeSave($insert);

}  

Check the docs for more info:

https://www.yiiframework.com/doc/api/2.0/yii-db-baseactiverecord#beforeSave()-detail

In order to show this date again in your forms and views:

<?php    
    $model->date_of_birth = !empty($model->date_of_birth) ? Yii::$app->formatter->asDate($model->date_of_birth, 'php:d-m-Y') : null;    
?>

UPDATED:

To make the filters work in (for example) the gridview when you have a formatted date, you should modify the comparison in your YourModelSearch.php model class:

From this:

$query->andFilterWhere(['date_of_birth' => $this->date_of_birth]);

To this:

/* Change the format to MySQL Date Format when compares */
$query->andFilterWhere(['date_of_birth' => Yii::$app->formatter->asDate($this->date_of_birth, 'php:Y-m-d')]);

And in your config/web.php you should have something like this:

'components' => [
    ...
    'formatter' => [
        'class' => 'yii\i18n\Formatter',
        'timeZone' => 'Europe/London',
        'defaultTimeZone' => 'Europe/London',
        'dateFormat' => 'd/M/Y',
        'datetimeFormat' => 'd/M/Y H:m',
        'timeFormat' => 'H:m:s',
        'decimalSeparator' => ',',
        'thousandSeparator' => '',
        'currencyCode' => 'EUR',
        'locale'=>'es_ES'
    ],
    ...
]

And never use the LIKE comparer with Date types.