Search between dates

4

I need to search my MySQL for some registered employees. I have the dt_nation field and I need, for example, to look for employees who are between 20 and 30 years old, how can I do that?

With this code snippet 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
    
asked by anonymous 21.05.2014 / 19:49

4 answers

0

After a lot of help, I'll also leave my collaboration, I did it in a simple way and it might 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
    
22.05.2014 / 20:59
5

Following SQL that takes into account the anniversary 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 visibility)

If you need to adjust the query to exclude or include the specific birthday, just use any of these settings (depending on whether you are going to use the first or last range):

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

or

      DATE_SUB(DATE_SUB(NOW(), INTERVAL 30 YEAR), INTERVAL 1 DAY)
    
21.05.2014 / 23:31
3

The question is about the age of the employees so the first SQL is the gross form for the year and the second one gives you a year with a rough estimate

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

Note:

    

21.05.2014 / 22:51
1

Based on Bacco's response, we can use the CURDATE() function of MySQL to return the current day, without the time information. So we do not have to worry about increasing or decreasing a deadline day.

Bacco's original 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 >= and < operators instead of BETWEEN

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

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

Query example in sqlfiddle .

    
22.05.2014 / 00:18