Clever Geek Handbook

SQL - Calculate age from year of birth

Brand new to SQL, slowly learning my way, but I'm very fixated on something I'm trying to do. Have tried browsing to figure it out, but I just can't figure it out.

Basically, I need to calculate the age from the year of birth and then multiply it by seven to get the "human age".

So I have two columns from which I need to get data called Dog_id and Name which comes from a table called Dogs. There is a dead column on this dog table that lists the YEAR that the dog died. Several years as an example of when dogs died are 2005 and 2008. The data displayed should be in the "Human Age" column

So far I had: Select dog_id, name FROM dogs WHERE DIED> '0'

What can I add to what I have above, set the current year, get the age since he died, and multiply by 7 to get the age of the dog people?

Any help would be greatly appreciated as I am very stuck.

Thank!

+3


source to share


3 answers


You can use DATE_SUB(NOW(), birth_year YEARS)

or DATE_ADD(NOW(), -birth_year YEARS)

to get the age and then immediately multiply by 7:

SELECT dog_id, name DATE_SUB(NOW(), birth_year YEARS)*7 AS Human_Age 
FROM dogs 
WHERE died > 0;


Another variant:

SELECT dog_id, name DATE_SUB(NOW(), (YEAR(NOW()) - birth_year)*7 AS Human_Age
FROM dogs 
WHERE died > 0;
0


source


SELECT dog_id, name, TIMESTAMPDIFF (YEAR, DIED, NOW ()) AS HUMAN_AGE FROM dogs;



You can use TIMESTAMPDIFF, its arguments are the unit you want the result to be expressed in, and the two dates you want to have a value for.

0


source


Try the following:

SELECT (YEAR(CURDATE()) - year) * 7 AS diedYearsAgo, Dog_id, Name
FROM Dogs
0


source






More articles:


All Articles