Search from between dates

Question

I need to search in my Mysql age of some registered collaborators. I have the field dt_nascimento and I need for example to find collaborators who are between 20 and 30 years old, how can I do this?

With this piece of code I get the dates, but unfortunately it’s not what I need, see:

SELECT (YEAR(CURDATE())-YEAR(dt_nascimento)) - (RIGHT(CURDATE(),5)<RIGHT(dt_nascimento,5)) as idade FROM candidato c

Answer 1

Follows SQL that takes into account the birthday date:

SELECT * FROM candidato
   WHERE dt_nascimento BETWEEN
      DATE_SUB(NOW(), INTERVAL 20 YEAR) AND
      DATE_SUB(NOW(), INTERVAL 30 YEAR)

_{(line breaks added for better ligibility)}

If you need to adjust the query to delete or include the specific birthday day, just use any of these settings (depending on whether you use it in the first or last interval):

      DATE_ADD(DATE_SUB(NOW(), INTERVAL 20 YEAR), INTERVAL 1 DAY)

or

      DATE_SUB(DATE_SUB(NOW(), INTERVAL 30 YEAR), INTERVAL 1 DAY)

Answer 2

The question is about the age of the collaborators so the first SQL is the gross form by the year and the second gives you a year with a more approximate calculation

SQL only with Years

SELECT * FROM candidato WHERE (YEAR(NOW()) - YEAR(dt_nascimento)) BETWEEN 20 AND 30

SQL with Years, Months and Days

SELECT CAST(SUBSTRING(datac, 1,2) AS unsigned) Anos, dt_nascimento           
FROM
(
SELECT dt_nascimento, ((date_format(now(), '%Y%m%d') - date_format(dt_nascimento,'%Y%m%d'))) as datac FROM candidato 
) as resultado where 
CAST(SUBSTRING(datac, 1,2) AS unsigned) BETWEEN 30 AND 40

Obs: Although the question does not say whether it wants an accuracy of months and days by the visa was only year I did both

Answer 3

Based on Bacco’s response, we can use the function CURDATE() Mysql to return current day without the time information. So we don’t have to worry about increasing or decreasing one day of the deadlines.

The original Bacco query would look like this:

SELECT data_nascimento FROM candidato
WHERE data_nascimento >= DATE_SUB(CURDATE(), INTERVAL 30 YEAR) AND
      data_nascimento < DATE_SUB(CURDATE(), INTERVAL 20 YEAR);

Another alternative is the use of operators >= and < instead of BETWEEN

Note: In case of use of operators, it will only work if the field data_nascimento is of the date type

SELECT data_nascimento FROM candidato
WHERE data_nascimento >= DATE_SUB(CURDATE(), INTERVAL 30 YEAR) AND
      data_nascimento < DATE_SUB(CURDATE(), INTERVAL 20 YEAR);

Example of the sqlfiddle.

Answer 4

After help from many, I will also leave my collaboration, I did it in a simple way and may also help someone, see:

select * from candidato WHERE (YEAR(CURDATE())-YEAR(candidato.dt_nascimento)) - (RIGHT(CURDATE(),5)<RIGHT(candidato.dt_nascimento,5)) between 38 and 45