Magento – Magento : Filtering Date Issue on Custom Grid

columndatefiltergridmagento-1

This is my column code:

$this->addColumn(
   Mage::getModel('catalog/product')->formatUrlKey($key),
   array(
       'header'   => Mage::helper('core')->__($label),
       'index'    => $key,
       'filter'   => $isFilterable,
       'sortable' => true,
       'type' => 'date',
       'filter_condition_callback' => array($this, 'dateFilter'),
   )
);

This is my callback code:

 public function dateFilter($collection, $column){
        $value = $column->getFilter()->getValue();
        $from = $value['orig_from'];
        $to   = $value['orig_to'];
        if (!$value) {
            return $this;
        }

        $this->getCollection()->getSelect()->where("date_scheduled >=DATE('".$from."') AND date_scheduled <= DATE('".$to."') ");
        return $this;
  }

I executed query in phpmyadmin and I got no errors, but neither results. I want to do is to filter my grid after date, so between X and Y.

The value for $isFilterable is adminhtml/widget_grid_column_filter_date

This is my Query generated:

SELECT `t`.*
FROM (SELECT
        DATE_FORMAT(`as`.`scheduled_at`, '%Y-%c-%d ')      AS `date_scheduled`,
        `o`.`customer_email`,
        `as`.`increment_id`                                AS `subscription id`,
        `as`.term                                          AS `period`,
        `c`.`increment_id`                                 AS `customer increment id`,
        `o`.`increment_id`                                 AS `order incrementid`,
        (SELECT SUM(`asi`.`price` * `asi`.`qty`)
         FROM `adyen_subscription_item` AS `asi`
         WHERE `asi`.`subscription_id` = `as`.`entity_id`) AS `subtotal_ex_vat`,
        (SELECT SUM(`asi`.`price_incl_tax` * `asi`.`qty`)
         FROM `adyen_subscription_item` AS `asi`
         WHERE `asi`.`subscription_id` = `as`.`entity_id`) AS `subtotal_inc_vat`,
        `s`.`code`                                         AS `store`,
        `as`.`shipping_method`                             AS `shipping_method`,
        `bill`.`method_code`,
        `bill`.`agreement_label`,
        GROUP_CONCAT(`oi`.`sku` SEPARATOR ',')             AS `product SKUs`
      FROM `adyen_subscription` AS `as` LEFT JOIN `sales_flat_order` AS `o` ON `o`.`entity_id` = `as`.`order_id`
        LEFT JOIN `adyen_subscription_item` AS `oi` ON `as`.`entity_id` = `oi`.`subscription_id`
        LEFT JOIN `customer_entity` AS `c` ON `o`.`customer_id` = `c`.`entity_id`
        LEFT JOIN `core_store` AS `s` ON `as`.`store_id` = `s`.`store_id`
        LEFT JOIN `sales_billing_agreement` AS `bill` ON `as`.`billing_agreement_id` = `bill`.`agreement_id`
      WHERE (`as`.`scheduled_at` >= now() AND `as`.`scheduled_at` <= DATE_ADD(now(), INTERVAL 6 MONTH)) AND
            `as`.`status` = 'active'
      GROUP BY `oi`.`subscription_id`
      ORDER BY `as`.`scheduled_at` ASC) AS `t`
WHERE (date_scheduled >= DATE('02/25/2017') AND date_scheduled <= DATE('02/28/2017')) 

Best Answer

Change type from date to datetime as shown below:

$this->addColumn(
 Mage::getModel('catalog/product')->formatUrlKey($key),
  array(
   'header'   => Mage::helper('core')->__($label),
   'index'    => $key,
   'filter'   => $isFilterable,
   'sortable' => true,
   'type' => 'datetime',
   'filter_condition_callback' => array($this, 'dateFilter'),
   )
 );