age from dob in sql

Question

In the sql query, I am getting problem in the where clause.

I have a dob and ssn of people in a table. I need to find the youngest snr. customer. The age of youngest senior customer starts from 55. The data of DOB contains all the dob's of children,parent, senior customers. In the line "where" I have to write a condition that checks if the age is > 55 and has to be smaller amongst the senior customers.Please suggest me some ways .I posted a similar question before, but did nt get any reply that can help me in solving it.

I dont have the age parameter in my table.

SSn    DOB
22   1950-2-2
21   1987-3-3
54   1954-4-7

I need to find the ssn corresponding to the age whcih has to be greater than 55 and smaller among above values .

Answer 1

If you have the DOB then you can easily calculate the age based on the current date:

WHERE DATE_ADD(DOB, INTERVAL 55 YEAR) < NOW()

This will add 55 years to the DOB and if it is greater than the current time, it's true. This would indicate they are at least 55 years of age.

Answer 2

In the script below, the sub-select finds the DOB of the youngest person 55 years of age or over; this is then used to find the corresponding SSN record(s). [This uses SQL Server syntax.]

SELECT yt.* 
FROM *yourtable* yt
INNER JOIN 
(
    SELECT MAX(DOB) AS DOB
    FROM *yourtable*
    WHERE DATEADD(year, 55, DOB) < getdate() 
) maxdob
ON maxdob.DOB = yt.DOB

n.b. you may find more than a single record if there is more than 1 person with the same DOB. If you want to force this single restriction, add a TOP 1 clause in your SELECT statement. hth