0

I want to filter some orders based on the date range so in SQL i use this query

SELECT  * FROM  `sales_order` WHERE 
    date(created_at) >= '2020-12-02'
    AND (date( created_at) <= '2020-12-16')

in magento i use

$orders->addFieldToFilter('date(created_at)', ['qteq' => $startDate->format('Y-m-d')]);
$orders->addFieldToFilter('date(created_at)', ['lteq' => $endDate->format('Y-m-d')]);

but it does not work and the result is like this instead

SELECT * from sales_order where (`DATE(created_at)` >= '2020-12-01') AND (`DATE(created_at)` <= '2020-12-16)

4
  • Did you check if there exists orders in this date range? Also, other filters,if any, are working on this order collection or not? Dec 18 '20 at 4:40
  • yes sure there are orders in this date range, the only problem is idk the way to use the date() in SQL in this filter Dec 18 '20 at 4:46
  • I've given a code in answer, try that. Dec 18 '20 at 4:54
  • try this magento.stackexchange.com/a/328352/82670
    – Msquare
    Dec 23 '20 at 9:54
0

Below code worked for me:

$now = new \DateTime();
$order->addFieldToFilter('created_at', ['lteq' => $now->format('Y-m-d H:i:s')])
        ->addFieldToFilter('created_at', ['gteq' => $now->format('Y-m-d H:i:s')]);
1
  • thanks, but it doesn't work too, i have tried this way too Dec 18 '20 at 5:08
0

Try to use this below code :

$orders->addAttributeToFilter('created_at', ['qteq' => $endDate->format('Y-m-d H:i:s')]);
$orders->addAttributeToFilter('created_at', ['lteq' => $endDate->format('Y-m-d H:i:s')]);

OR

$orders->addAttributeToFilter('created_at', ['qteq' => strtotime($endDate->format('Y-m-d'))]);
$orders->addAttributeToFilter('created_at', ['lteq' => strtotime($endDate->format('Y-m-d'))]);

Updated :

$startDate = date("Y-m-d h:i:s",strtotime('2020-12-02'));
$endDate = strtotime("Y-m-d h:i:s", strtotime('2020-12-16'));
$orders->addAttributeToFilter('created_at', ['from' => $startDate, 'to' => $endDate]);
4
  • thanks, but it gives me empty result and the query last like this AND (((main_table.created_at = '1607385600'))) AND (main_table.created_at <= 1607472000) Dec 18 '20 at 5:15
  • Try with updated answer. Dec 18 '20 at 5:25
  • it works but, the result is not true, i mean in that date range it have to have 3 orders but this only shows 2 orders, this filter does not get the orders in the $endDate and this is the reason i use date('created_at') since this gives me 3 orders but unfortunately the syntax for date(''created_at) is wrong. Dec 18 '20 at 5:38
  • put the start date as 2020-12-01 and check it. Dec 18 '20 at 5:52
0

Try this,

$startDate = date("Y-m-d h:i:s",strtotime('2020-12-02')); // start date
$endDate = strtotime("Y-m-d h:i:s", strtotime('2020-12-16')); // end date
     
$orders->addAttributeToFilter('created_at', array('from'=>$startDate, 'to'=>$endDate));
0
    public function getSalesOrder($yourDate = null)
 {  
    $date3 = str_replace('/', '-', $yourDate);
    $date2 = date('Y-m-d',strtotime($date3));
    $date1 = str_replace('-', '/', $date2);
    $tomorrow = date('Y-m-d',strtotime($date1 . "+1 days"));
    $db =$this->db1('sales_order');
    $sql = "SELECT * FROM sales_order WHERE created_at BETWEEN '$date2' AND '$tomorrow'";
    $base = ($db['connection']->fetchAll($sql));
    return count($base);
         
}

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.