How to filter a collection based on day (not date)?

Question

The ultimate goal is to get a list of customers whose birthday is today and send them a voucher code (which is also executed programmatically).

To do this, I need all customers today to get a birthday. In SQL, I would convert dateto day and month and query this, but I'm not sure how to do this in the collection. I currently have:

$collection = Mage::getResourceModel('customer/customer_collection')
        ->joinAttribute('dob','customer/dob', 'entity_id');
        ->addAttributeToFilter('dob', '2015-10-02 00:00:00');

This only applies to people with DOB 2/10/2015. In this example, I need to match everyone with the birth date of October 2, and not just October 2, 2015. So, how can I omit the year and just include the day and month?

I was able to achieve this using LIKE, but it seems unoptimized, and I would suggest that this is a much better way to do this:

$collection = Mage::getResourceModel('customer/customer_collection')
        ->joinAttribute('dob','customer/dob', 'entity_id')
        ->addAttributeToFilter('dob', array('like' => '%-10-02 00:00:00'));

Answer 1

:

SELECT
  emp_firstname AS first_name,
  emp_lastname AS last_name,
  emp_birthday AS dob
FROM
  emp
WHERE
  MONTH(emp_birthday)=MONTH(CURDATE())
  AND DAY(emp_birthday)=DAY(CURDATE())
or if you want to consider also leap years, you could use this:

SELECT
  emp_firstname AS first_name,
  emp_lastname AS last_name,
  emp_birthday AS dob
FROM
  emp
WHERE
  emp_birthday +
    INTERVAL
      YEAR(CURDATE())-YEAR(emp_birthday) +
      (MONTH(emp_birthday)<MONTH(CURDATE())
       OR (MONTH(emp_birthday)=MONTH(CURDATE()) AND DAY(emp_birthday)<DAY(CURDATE())))
    YEAR = CURDATE()
If someone date of birth is on 29th of February, and today is 28th of February and this year is not a leap year, my last query will consider his/her birthday as today.

My second query could be also simplified like this:

SELECT
  emp_firstname AS first_name,
  emp_lastname AS last_name,
  emp_birthday AS dob
FROM
  emp
WHERE
  (MONTH(emp_birthday)=MONTH(CURDATE())
  AND DAY(emp_birthday)=DAY(CURDATE()))
  OR (DAY(LAST_DAY(emp_birthday))=29
      AND DAY(emp_birthday)=29
      AND DAY(LAST_DAY(CURDATE()))=28);

Answer 2

solution:


require_once '../app/Mage.php';
Mage::app();

$currentDate = date('Y-m-d H:i:s');
$customerCollection = Mage::getResourceModel('customer/customer_collection')
        ->addNameToSelect()
        ->addAttributeToSelect('*')
        ->addAttributeToFilter('dob', array('lteq' => $currentDate));


foreach ($customerCollection as $customer) {
    $active = $customer->getIsActive();
    if($active == 1){
        //echo 'customer Dob => '.$customer->getDob().'<br>';
        $customerDob = $customer->getDob();
        $customerId = $customer->getId();
        $date =  date('d/m',  strtotime($customerDob));
        echo $customerId.' => '.$date.'<br>';
    }

}