Recover database records by date field?

1

How do I retrieve users saved in a database table by field dt_nascimento through two values that represent the minimum and maximum age of users that should be returned, the minimum and maximum age does not always come together can be specified only the minimum age, or only the maximum age. Is subquery required? Note: the database is mysql.

Basically this would be the query (where 18 and 30 are the age limits) the date of birth is in the American AAAA / MM / DD format the where clause is just an example, it does not work.

SELECT nome, dt_nascimento FROM usuario WHERE dt_nascimento > 18 AND dt_nascimento < 30;
    
asked by anonymous 28.04.2015 / 16:55

2 answers

2

Add 18 years and 30 years to date of birth and compare with current date.

SELECT nome, dt_nascimento 
FROM tbl_tabela1 
WHERE DATE_ADD(dt_nascimento,INTERVAL 18 YEAR) <= CURDATE()
AND DATE_ADD(dt_nascimento,INTERVAL 30 YEAR) >= CURDATE();  

If your age + 18 years is the current date is because you are under 30 years old.

    
28.04.2015 / 17:28
3

I understand that you can simply do the following:

select nome, dt_nascimento 
from tbl_tabela1
where TIMESTAMPDIFF(YEAR, dt_nascimento , CURDATE()) between 18 and 30

I'm assuming the date is stored in the database in the appropriate format. If you are not you can always convert (CAST) to date or datetime.

TIMESTAMPDIFF can still return the difference between two dates in DAYS or MONTHS, just replace YEAR with MONTH or DAY

    
28.04.2015 / 17:10