how SELECT people within age range

Question

I've already tried all other questions/solutions, and no answer. so here it is: I need to SELECT * FROM people WHERE (dob is 18 to 40)

but my dob is stored as DATE type YYYY-MM-DD

need to select people between 18 and 40 for example!

So how do you compute somebody's age, if you have his date of birth? — uselpa, Nov 29 '13 at 20:02

M Khalid Junaid · Accepted Answer · 2013-11-29T20:13:01.987

3

You need to use BETWEEN with some year calculation TIMESTAMPDIFF(YEAR,'1980-02-04',NOW())

SELECT * FROM people WHERE TIMESTAMPDIFF(YEAR,`dob`,NOW()) BETWEEN  18 AND 40

edited Nov 29 '13 at 20:13

answered Nov 29 '13 at 20:04

M Khalid Junaid

1

+1 This is the simplest way, but you are not using the YEAR(date) function in there, why put that link there? – Filipe Silva Nov 29 '13 at 20:12
as long as it's mysql 5.0+ this will work. – gillyspy Nov 29 '13 at 20:14
@FilipeSilva i want the difference in years so if you see an example here http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff – M Khalid Junaid Nov 29 '13 at 20:16
@gillyspy yes taken example from mysql 5.5 manual – M Khalid Junaid Nov 29 '13 at 20:17
@dianuj FYI, since `now()` is timestamp (has a time component) one should avoid doing arithmetic of this type with `now()` but instead use `current_date` – gillyspy Nov 29 '13 at 20:37
1

thank you so much! it worked – Nov 29 '13 at 20:42
@dianuj. Yes. the timestampdiff thing is why i upvoted. but your second link YEAR(date) is unrelated. that's a function to get the year from a date. You are not using that. – Filipe Silva Nov 29 '13 at 21:14
@FilipeSilva its only for showing how to get the year from date no use of it at all but added as reference – M Khalid Junaid Nov 29 '13 at 21:17

score 1 · Answer 2 · answered Nov 29 '13 at 20:01

1

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

answered Nov 29 '13 at 20:01

S Melikian

score 1 · Answer 3 · answered Nov 29 '13 at 20:03

1

You have to calculate 40 years ago from today, and 18 years ago from today. Let's say it was 1940-01-01 and 1980-01-01, then it would be:

WHERE dob BETWEEN '1940-01-01' AND '1980-01-01';

answered Nov 29 '13 at 20:03

redolent

gillyspy · Answer 4 · 2013-11-29T20:14:04.517

0

Since age is relative to today:

select * from people where 
date_sub(curdate(), interval 40 year)  =< dob
and date_sub(curdate(), interval 18 year) >= dob

This will work on any version of mysql

edited Nov 29 '13 at 20:14

answered Nov 29 '13 at 20:05

gillyspy

score 0 · Answer 5 · answered Nov 29 '13 at 20:06

since I do not know the SQL dialect you use I take the full variant

since age is now - birth (!)

select * from people where
    current date - date(birth) > MINAGE
    and
    current date - date(birth) < MAXAGE

current date depends on SQL you are using

5 Answers5