I display my date in CGridView as: "26.6.2012 22:36" with:
array('name' => 'date',
'value' => date("j.n.Y G:i", strtotime($model->date))
),
But in my filter, I need to search in this format (which is in the database) to get results: "2012-06-26 22:36".
How can I make my filter to work in the format that is displayed in my CGridView? I’ve already gotten a proposal to use a datetime picker, but with that I can’t just search by time without date.
Help would be greatly appreciated.
If you use ‘dataProvider’ => $model->search() in your gridview, then customize search() method in relevant model.
E.g.,
$criteria->compare('date',$this->date, true);
Compare() docs
If you want to catch $this->date from filter in other format, then it’s stored in db (as I understood from your post), then you may change custom format to db format before performing a compare.
Hello,
compare() makes a sql sentence with the input, so I had to change the input to my wanted format.
my function:
function changeDateToDBformat($datum) {
if (strstr($datum, '.') || strstr($datum, ':')) {
$formats = array('!j.n', '!j.n.Y', '!j.n.Y H:i', '!n.Y H:i', '!n.Y', '!H:i', '!j.n.Y H', '!n.Y H', '!Y H:i', '!Y H');
$date = false;
foreach ($formats as $format) {
$date = DateTime::createFromFormat($format, $datum);
if (!($date === false)) {
$izbraniFormat = $format;
break;
}
}
if (!$date === false) {
$datum1 = $date->format('Y-m-d H:i');
$date2 = DateTime::createFromFormat(substr($izbraniFormat, 1, strlen($izbraniFormat)), $datum);
$datum2 = $date2->format('Y-m-d H:i');
$datumcas1 = explode(' ', $datum1);
$datumcas2 = explode(' ', $datum2);
$prvidatum = explode('-', $datumcas1[0]);
$drugidatum = explode('-', $datumcas2[0]);
$koncniDatum = '';
for ($a = 0; $a < sizeof($prvidatum); $a++) {
if ($prvidatum[$a] == $drugidatum[$a])
$koncniDatum .= '-' . $prvidatum[$a];
}
$koncniCas = '';
$prvicas = explode('-', $datumcas1[1]);
$drugicas = explode('-', $datumcas2[1]);
for ($a = 0; $a < sizeof($prvicas); $a++) {
if ($prvicas[$a] == $drugicas[$a])
$koncniCas .= ':' . $prvicas[$a];
}
$koncniDatum = substr($koncniDatum, 1, strlen($koncniDatum));
if (strlen($koncniCas) > 0)
$koncniDatum .= ' ' . substr($koncniCas, 1, strlen($koncniCas));
$datum = $koncniDatum;
}
}
return $datum;
}
//translations:
//datum == date
//cas == time
//izbrani == selected
//koncni == end
//prvi == first
//drugi == second
With this, a user can enter date in the format "j.n.Y H:i" and also just portions of this format (j.n, n.Y, Y H:i,…).
This works great, but how are you handling greater than / less than filtering?
You can do searches in Yii CGridView’s like the following:
>2012-06-28
which work great, but I’m in the same boat as you, trying to allow filtering using custom date formats:
>6/15/12
I can’t just format that string to the DB equivalent, as the > sign causes issues. Thoughts?
I didn’t even know about that function 
after if (strstr($datum, ‘.’) || strstr($datum, ‘:’)) {
chech if $datum has ‘>’ or ‘<’ in it
if it has, then check which one it is and remove it from $datum
at the end, before you return $datum from the function, add the special char back to the start of the string.
That should work
Thanks for the reply. I looked into it further yesterday and ended up copying over some of the code from the CDbCriteria compare function. I ended up with this:
public function search() {
$criteria = new CDbCriteria;
....
$criteria->compare('StartDate', $this->dbDateSearch($this->StartDate), true);
....
}
private function dbDateSearch($value)
{
if($value != "" && preg_match('/^(?:\s*(<>|<=|>=|<|>|=))?(.*)$/',$value,$matches))
return $matches[1] . date("Y-m-d",strtotime($matches[2]));
else
return $value;
}
Long-term, it seems like the type of thing to either put in your own ActiveRecord override or in a CDbCriteria override object so that it’s easier to use.
hello All,
If you want to search date in d-m-y format in gridview
then use below code,
private function dbDateSearch($value)
{
if($value != "" && preg_match('/^([1-9]|[1-2][0-9]|3[0-1])-([1-9]|1[0-2])-[0-9]{4}$/', $value,$matches))
return date("Y-m-d",strtotime($matches[0]));
else
return $value;
}
thanks.
here is the other way,
If you want to search date in d-m-y format in gridview
$criteria->compare('DATE_FORMAT(date,"%d-%m-%y")',$this->date,true);
hope usefull