CODEWITHSUNDEEP
mysql
user2251695
user2251695

Reputation: 129

calculate Age from year, month and day fields

I have a table of people and I need to know how many of them are actual minors.

I have the following query:

SELECT count(*) as minors from 
FilesMain a INNER JOIN Sides b
ON a.FileID = b.FileID 
INNER JOIN SideData c 
ON b.SideDataID = c.SideDataID
WHERE a.StatusCode IN (100,101) AND (YEAR(CURDATE()) - BirthYear<17)

Basically in the query above, I am calculating current date year minus BirthYear field.

I have the persons birth date separated to year, month and day in 3 different fields. please don't ask why. I inherited the data. What would be the correct way to use the Month and Day fields as well to get a more specific result. Just using Year will treats someone born January first and December 31 the same.

Thanks

Upvotes: 0

Views: 118

Answers (1)

Akina
Akina

Reputation: 42622

... AND TIMESTAMPDIFF(YEAR, 
                      CONCAT_WS('-', BirthYear, BirthMonth, BirthDay), 
                      CURRENT_DATE) < 17

Also you may add generated column:

ALTER TABLE tablename
ADD COLUMN DOB DATE 
    GENERATED ALWAYS AS (CONCAT_WS('-', BirthYear, BirthMonth, BirthDay));

and use this column instead of the above expression.

Upvotes: 2

Related Questions