Working with Date fields on MySQL


I’m storing date fields as MySQL’s DATETIME which expects the date to be formatted as ‘YYYY-MM-DD’,

but for display and input purposes I’m using ‘Y/m/d’ format (the default value of CFormatter.dateFormat) as it is displayed in Grids, DetailViews etc.

For consistency, in the search form I’d like to keep the Y/m/d format, so there is the rule defined:

array('search_date_from, search_date_to', 'date', 'format' => 'yyyy/MM/dd', 'on' => 'usersearch'),

and the search function:

        if (!empty($this->search_date_from))


            $criteria->addCondition('t.date_created >= :from');

            $criteria->params['from'] = $this->search_date_from;


        if (!empty($this->search_date_to))


            $criteria->addCondition('t.date_created < DATE_ADD(:to, INTERVAL 1 DAY)');

            $criteria->params['to'] = $this->search_date_to;


how can I make sure MySQL interprets the input as proper date? What happens if I change the input format? How do I know whether MySQL interpreted this properly?

I know this topic has been discussed but I find it really difficult, also because of inconsistency in PHP’s date() and Yii’s date validation patterns…