Special Price From Date and To Date

Question

I'd like to directly query sku, special price, special price from date, special price to date from database.

We are offering special sale every week with different items. Sale items are supposed to have from date and to date. Unfortunately many of them are missed to date. We haven't cleared old/previous sale information for a long time. So I don't know how many items are on sale now.

Please advise me.

Answer 1

You may use following code

$collection=Mage::getResourceModel('catalog/product_collection')
            ->addAttributeToSelect('name')
            ->addAttributeToSelect('sku')
            ->addAttributeToSelect('special_price')
            ->addAttributeToSelect('price');
$collection->addAttributeToFilter('special_from_date',array(array('from' => Mage::getModel('core/date')->gmtDate()),
                                 array('special_from_date', 'null'=>true)));
$collection->addAttributeToFilter('special_to_date',array(array('to' => Mage::getModel('core/date')->gmtDate()),
                                 array('special_to_date', 'null'=>true)));

Answer 2

Use below code :

$_productCollection = Mage::getModel('catalog/product')->getCollection();
$_productCollection->addAttributeToSelect(array(
                                   'sku',
                                   'name'
                   ))
                   ->addFieldToFilter('visibility', array(
                               Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH,
                               Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG
                   )) 
                   ->addFinalPrice()                       
                   ->getSelect()
                   ->where('price_index.final_price < price_index.price');

with the above collection, you will get list of products which have sale. You can also run this code from Magento root testing file so you get idea which products have sale or other information.

For information print, you need to code :

<?php foreach ($_productCollection as $_product): ?>
[Display Info What You want]
<?php endforeach; ?>