5

Here is my sql query

select * from my_table where status = 1 and CURDATE() NOT BETWEEN start_date and end_date

I tried the following code but it is giving me wrong answer when both dates are NULL or any of them. I want to compare the date if it is available.

$currentDate = date('Y-m-d');
Mage::getModel('mymodule/module')
            ->getCollection()
            ->addFilter('status',1)
            ->addFieldToFilter(
                array('start_date', 'end_date'),
                array(
                    array('gt' => $currentDate),
                    array('lt' => $currentDate),

                )
            );

1 Answer 1

3

The original answer is for BETWEEN. For NOT BETWEEN see update below

For the collection filter operators, it's always useful to take a look at Varien_Db_Adapter_Pdo_Mysql::prepareSqlCondition() which is well documented:

/**
 * Build SQL statement for condition
 *
 * If $condition integer or string - exact value will be filtered ('eq' condition)
 *
 * If $condition is array is - one of the following structures is expected:
 * - array("from" => $fromValue, "to" => $toValue)
 * - array("eq" => $equalValue)
 * - array("neq" => $notEqualValue)
 * - array("like" => $likeValue)
 * - array("in" => array($inValues))
 * - array("nin" => array($notInValues))
 * - array("notnull" => $valueIsNotNull)
 * - array("null" => $valueIsNull)
 * - array("gt" => $greaterValue)
 * - array("lt" => $lessValue)
 * - array("gteq" => $greaterOrEqualValue)
 * - array("lteq" => $lessOrEqualValue)
 * - array("finset" => $valueInSet)
 * - array("regexp" => $regularExpression)
 * - array("seq" => $stringValue)
 * - array("sneq" => $stringValue)
 *
 * If non matched - sequential array is expected and OR conditions
 * will be built using above mentioned structure
 *
 * @param string|array $fieldName
 * @param integer|string|array $condition
 * @return string
 */

Unfortunately there is no operator for BETWEEN, so you will have to either use $collection->getSelect() to manipulate the query directly or use a combination of < and > instead, which can be built using the from and to keys. For date comparison, there is even an additional date or datetime key that will make sure that the from and to parameters are formatted as dates1.

But in your case, not the field itself should be "between" to parameters but the current date between two fields, so we cannot use from, to and datetime. Instead we call addFieldToFilter() twice so that the conditions are combined with AND:

->addFieldToFilter(array('start_date' => array('lt' => $curDate))
->addFieldToFilter(array('end_date' => array('gt' => $curDate));

To really use CURDATE() from MySQL and avoid trouble from timezone settings in PHP, initialize $curDate like this:

$curDate = new Zend_Db_Expr('CURDATE()');

Update:

Since you were asking for NOT BETWEEN, you need to switch lt and gt and add both conditions in a single addFieldToFilter call to combine them with OR:

->addFieldToFilter(array(
    'start_date' => array('gt' => $curDate),
    'end_date' => array('lt' => $curDate)
));

This will also find records where start_date is NULL and end_date is in the past (and vice versa), but not records where start_date and end_date are both NULL. This makes sense if start_date and end_date follow the same semantics as in core Magento and you want to find all records that are currently not within the range.

So you already were close to the solution but passed the parameters in a wrong form.


1) In theory, 'date' => true should format the parameter as date and 'datetime' => true as date and time. But they forgot to pass the $includeTime parameter to formatDate() in _prepareSqlDateCondition(), so they both use the default format (date and time)

2
  • Thanks for the detail answer. I think we can only use OR to achieve the expected result ->addFieldToFilter( array('start_date', 'end_date'), array( array('gt' => $currentDate), array('lt' => $currentDate), ) ); But the above filter does not consider the records that have NULL value of start_date or end_date column. I want those record as well. Aug 12, 2015 at 18:50
  • Please see update Aug 13, 2015 at 6:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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