Compare day and month of a date?

Question

In the Mysql I have saved a person’s birth date, I need to assemble a query that return me the birthday of the week, those who make today’s birthday to today + 7 days.

I’m trying like this:

//Metodo da tela de listagem
public function birthdayDeed()
{
    // construtor de query
    $criteria = CriteriaBuilder::create()
    // seleciono a tabela
        ->tables('clients')
    // adiciono uma condição da data ser maior ou igual a de hoje
        ->_and('birth', '>=', date('Y-m-d', time()))
    // E se data for menor ou igual daqui a 7 dias
        ->_and('birth', '<=', date('Y-m-d', strtotime("+7 days"))); 


    $this
        ->title( Language::get('client', 't-birthday') )
        ->view('client', 'list')
        ->attr('clients', $this->toArray( (new Client())->debug()->listAll(null, $criteria) ))
        ->display();
}

Which results in the query:

'SELECT * FROM  clients   WHERE birth >=  :birth  AND birth <= :birth1;'

I know where the error is occurring, I’m also comparing the year, so would be for example:

birth: 1996-10-30
condição: 1996-10-30 >= 2016-10-28 AND 1996-10-30 <= 2016-11-04

Soon I would never enter this condition, I need to know how to compare only the day and month, someone could help?

Answer 1

Make the comparison by extracting the right bits (day and month) of the date, in mysql you can use the functions day() and month().

Your code should look like this:

->_and('day(birth)', '>=', date('d'))
->_and('day(birth)', '<=', date('d', strtotime("+7 days"))
->_and('month(birth)', '=', date('m')

Answer 2

With the answers, I could understand more and research a little more on the subject, I found a solution joining a series of similar questions:

SELECT * FROM clientsWHERE 
     DATE_FORMAT(birth,'%m-%d') >= DATE_FORMAT(NOW(),'%m-%d') AND
     DATE_FORMAT(birth,'%m-%d') <= DATE_FORMAT(NOW() + INTERVAL 7 DAY,'%m-%d')

Answer 3

SELECT  *
FROM table
WHERE birth between date_add(updated_at, interval year(now())-year(updated_at) year) and
                    date_add( date_add(updated_at, interval year(now())-year(updated_at) year) , interval 7 day)