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? Commented Dec 18, 2020 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 Commented Dec 18, 2020 at 4:46
  • I've given a code in answer, try that. Commented Dec 18, 2020 at 4:54
  • try this magento.stackexchange.com/a/328352/82670
    – Msquare
    Commented Dec 23, 2020 at 9:54

4 Answers 4

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 Commented Dec 18, 2020 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) Commented Dec 18, 2020 at 5:15
  • Try with updated answer. Commented Dec 18, 2020 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. Commented Dec 18, 2020 at 5:38
  • put the start date as 2020-12-01 and check it. Commented Dec 18, 2020 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 and acknowledge you have read our privacy policy.

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