Compare day and month of a date?

Navigation
3

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
        ->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();
}

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?

    
asked by anonymous 28.10.2016 / 16:22

3 answers

1

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: 

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')
    
28.10.2016 / 17:24
3

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')
    
28.10.2016 / 16:43
0
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)
    
28.10.2016 / 16:51
Firedac -How to use same FDquery in more than one bank? How to work with lock in SQL records?