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
->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?
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:
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')
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')
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)