3

I have below code

$updatedAtFrom = date("Y-m-d",strtotime($updatedAt['orig_from']));
$updatedAtTo = date("Y-m-d",strtotime($updatedAt['orig_to']));
$this->getCollection()->addFieldToFilter('DATE(main_table.updated_at)', array('from' => $updatedAtFrom,'to' => $updatedAtTo));

When I run query it displays

(`DATE(main_table`.`updated_at)` >= '2017-04-17' AND `DATE(main_table`.`updated_at)` <= '2017-04-17')

So it gives #1054 - Unknown column 'DATE(main_table.updated_at)' in 'where clause'

When I remove DATE then it works fine. I have that field as type timestamp. But I want to compare only Date.

2

[NOT TEST YET]
You can try with Zend_Db_Expr.

$queryExpr = new \Zend_Db_Expr("DATE(main_table.updated_at) >= '{$updatedAtFrom}' AND DATE(main_table.updated_at) <= '{$updatedAtTo}'");

$this->getCollection()->getSelect()->where($queryExpr);
| improve this answer | |
0

You need to format date in Y-m-d H:i:s format. Then use collection filter as follows.

$this->getCollection()->addFieldToFilter('updated_at', ['lteq' => $datetime->format('Y-m-d H:i:s')])
    ->addFieldToFilter('updated_at', ['gteq' => $datetime->format('Y-m-d H:i:s')]);

Where $datetime is object of DateTime.

| improve this answer | |
  • I need to only compare with DATE(updated_at). Need right syntax – Jackson Apr 19 '17 at 6:01
0

You can try with Zend_Db_Expr.

$updatedAtFrom = date("Y-m-d",strtotime($updatedAt['orig_from']));
$updatedAtTo = date("Y-m-d",strtotime($updatedAt['orig_to']));
$this->getCollection()->addFieldToFilter(new \Zend_Db_Expr("DATE(main_table.updated_at)"), array('from' => $updatedAtFrom,'to' => $updatedAtTo));
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.