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)