-4

How to calculate maximum age of a male person from a list containing date of birth in sql?

I tried

Select name, datediff(yy, dateofbirth, getdate()) as age
From table 
Where gender = male

But I don’t know how to use the max function to get the max age

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Charu
  • 3
  • 1
  • Which dbms are you using? (datediff and getdate are product specific functions.) – jarlh May 30 '23 at 13:29
  • 1
    A [mcve] is a great start when asking for SQL assistance. – jarlh May 30 '23 at 13:29
  • Welcome to Stack Overflow! Often people asking questions such as yours include, as text, sample data for their tables and a sample of the desired result. Please [edit] your question. – O. Jones May 30 '23 at 13:30
  • Remove the column name and put a max() around you datediff – nbk May 30 '23 at 13:31
  • 2
    Or use order by age deac and limit it to one – nbk May 30 '23 at 13:32
  • What's the expected result if there are two men both having same oldest age? – jarlh May 30 '23 at 13:43
  • I am using azure – Charu May 30 '23 at 15:16
  • A simple datediff in years will not accurately calculate the age of a person. If you are trying to find the oldest individual in your set - then just select the person with the oldest date of birth. No need to calculate the age, because someone with a DOB of 05/30/1953 will always be older than someone with a DOB of 06/01/1953. – Jeff May 30 '23 at 18:19
  • Once you have that persons DOB - then calculate their age as of a specific point in time. For example, you want to calculate the persons age as of today - given a DOB of 06/01/1953 and you should get 69 because they are not yet 70. So you need to check if the month and day of the DOB is greater than the month and day of today - and if so, subtract one. – Jeff May 30 '23 at 18:24

2 Answers2

1

Do something like this to get the least recent date of birth.

SELECT name, datediff(yy, dateofbirth, getdate()) as age, dateofbirth
  FROM tbl
 WHERE gender = 'm'
 ORDER BY dateofbirth
 LIMIT 1

If you use Microsoft SQL Server, you do this.

SELECT TOP (1)
       name, datediff(yy, dateofbirth, getdate()) as age, dateofbirth
  FROM tbl
 WHERE gender = 'm'
 ORDER BY dateofbirth
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • No it didn’t work – Charu May 30 '23 at 15:12
  • "It didn't work" is not a useful trouble report. What went wrong? A syntax error? An incorrect result? Did your server catch fire and burn down your data center? **Pro tip** every hour you spend learning to read error messages will pay back a hundredfold over your career. – O. Jones May 30 '23 at 15:16
  • Says incorrect syntax near limit – Charu May 30 '23 at 15:18
  • @Charu, this is what happens when you don't add a tag for the dbms. You get something that works, but not for you - because you're using another dbms than the person answering. – jarlh May 30 '23 at 17:31
0

If we assume everyone in the table is still alive, then the oldest male has the MIN(DOB). You did not specify any date of death so there you go.

Then you only need to calculate their age.

Something along the lines of:

WITH CTE AS (
SELECT MIN(DOB) AS [minDOB]
FROM table
WHERE gender = 'male'
) --CTE
SELECT *
      ,[Age] = floor((datediff(day,0,@today) - datediff(day,0,@birthdate)) / 365.2425) 
  FROM table
 WHERE DOB = (SELECT [minDOB] FROM CTE) AND gender = 'male'

For the age algorithm I used brianary's answer on a different stackoverflow thread at How to calculate age (in years) based on Date of Birth and getDate()

Feel free to upvote his answer.

Zorkolot
  • 1,899
  • 1
  • 11
  • 8