Compare day and month of a date?

Posted on

Question :

In MySQL I have saved the date of birth of a person, I need to set up a query to return the birthdays of the week, those that make today’s birthday until today + 7 days. p>

I’m trying like this:

//Metodo da tela de listagem
public function birthdayDeed()
    // construtor de query
    $criteria = CriteriaBuilder::create()
    // seleciono a tabela
    // 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"))); 

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

What 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 it 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, could anyone help?


Answer :

With the answers, you gave to understand more and to research a little more on the subject, I found a solution putting together a series of similar questions:

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


Make the comparison by extracting the right bits (day and month) from 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')


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)


Leave a Reply

Your email address will not be published. Required fields are marked *