How to Determine the Age of a Person in aTable

Question

I am trying to determine the age of the people that are listed in my database. This is what I came up with, but I can't seem to limit the query to show people who are only 18 and under.

SELECT 
  * 
FROM 
  patientdetails 
WHERE 
  ageInYears IN (
    SELECT 
      DATEDIFF(CURRENT_DATE, STR_TO_DATE(p.DOB, '%d-%m-%Y'))/365 AS ageInYears 
    FROM 
      patientdetails p
  ) < 19

score 1 · Accepted Answer · answered Oct 01 '12 at 19:38

1

I believe something like this should work:

SELECT * 
FROM patientdetails 
WHERE DATEDIFF(CURRENT_DATE, STR_TO_DATE(p.DOB, '%d-%m-%Y'))/365 < 19

answered Oct 01 '12 at 19:38

Jordan Kaye

2,837
15
15

Zero results found. I know that this does return values though. SELECT DATEDIFF(CURRENT_DATE, STR_TO_DATE(p.DOB, '%d-%m-%Y'))/365 AS ageInYears FROM patientdetails p – Kevin Weber Oct 01 '12 at 20:08
Can you post some example results from that query? – Jordan Kaye Oct 01 '12 at 20:09
NVM, I see what I did wrong, your query is good. Thanks ^^ – Kevin Weber Oct 01 '12 at 20:13

score 0 · Answer 2 · answered Oct 01 '12 at 19:38

0

You don't need the subquery:

SELECT *
FROM PatientDetails pd
WHERE DATEDIFF(CURRENT_DATE, STR_TO_DATE(p.DOB, '%d-%m-%Y'))/365 <19

answered Oct 01 '12 at 19:38

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Oct 22 '12 at 02:53

This sort of works:

SELECT *
FROM PatientDetails pd
WHERE DATEDIFF(CURRENT_DATE, STR_TO_DATE(pd.DOB, '%d-%m-%Y')) / 365 < 19

Dividing the days by 365 will fail to accommodate for leap years, meaning you will be off by 4 days guaranteed. You could divide by 365.25 and then subtract 0.75. I think that accounts for everything, but I'm shooting from the hip a bit. I'm not sure offhand if there would be integer errors with this calculation or not. I'm not sure how MySQL handles implicit typecasting.

I would be more inclined to do it this way. Logically, it's "all patients whose 19th birthday is in the future":

SELECT *
FROM PatientDetails pd
WHERE DATE_ADD( STR_TO_DATE(pd.DOB, '%d-%m-%Y'), INTERVAL 19 YEAR ) > CURDATE()

However, this exact question is addressed and explained in the MySQL documentation for date calculations, but it seems kind of ass-backwards to me:

SELECT name, birth, CURDATE(),
(YEAR(CURDATE())-YEAR(birth))
- (RIGHT(CURDATE(),5)<RIGHT(birth,5))
AS age
FROM pet

How to Determine the Age of a Person in aTable

3 Answers3