Magento add filter for date

Question

Is there anyway that we can add between . say i need to retrieve the record to see if today falls in between the start date and end date

SELECT main_table.* FROM sample_table AS main_table WHERE (status = '1') AND '2016-09-27 20:25:14' BETWEEN start_date AND end_date

Answer 1

You can try below

            $yourObjectReference->addFieldtofilter('start_date', array(
                array('to' => Mage::getModel('core/date')->date()),
                array('start_date', 'null'=>'')
            ))
            ->addFieldtofilter('end_date', array(
                array('gteq' => Mage::getModel('core/date')->date()),
                array('end_date', 'null'=>'')
            ));

Answer 2

You can try below code for that

$collection = Mage::getModel('YourModel/ModelClass')->getCollection();
$collection->addFieldToFilter('start_date', array('gteq' =>$first));
$collection->addFieldToFilter('end_date', array('lteq' => $last));