How to use NOT BETWEEN SQL with magento collection

Question

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),

                )
            );

Answer 1

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 dates¹.

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)}