How to calculate age in query?

Question

How to calculate the age of the user inside a SQL query. Be aware I splitted the database into the fields: birthday, birthmonth and birthyear.

I created a fiddle to show what I mean.

CREATE TABLE IF NOT EXISTS `user_profile` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_birthday` tinyint(4) NOT NULL,
  `user_birthmonth` tinyint(4) NOT NULL,
  `user_birthyear` smallint(6) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `user_profile` 
(user_birthday, user_birthmonth, user_birthyear) VALUES (31, 12, 1990)

Which DBMS are you using? In Postgres you can use the `age()` function — , Oct 13 '15 at 19:01
The logic you want is, first, create a date from the three fields. Next, get the number of days between that date and the current date. Divide by 365.25. The age is the integer portion of the result. I don't use mySql so I don't know necessary functions. — Dan Bracuk, Oct 13 '15 at 19:08

score 1 · Accepted Answer · answered Oct 13 '15 at 18:59

1

SqlFiddleDemo

SELECT YEAR(NOW()) - YEAR(birthday) - (DATE_FORMAT(birthday, '%m%d') < DATE_FORMAT(NOW(), '%m%d')) AS Age
FROM (
SELECT
 CAST(CONCAT(`user_birthyear`, '-', `user_birthmonth`, '-', `user_birthday`) AS DATE) AS birthday
FROM user_profile ) AS s

answered Oct 13 '15 at 18:59

Lukasz Szozda

162,964
23
234
275

1

Awesome, this did the job. Thank you so much! – Piet Oct 14 '15 at 14:54
@Piet Happy to hear it helps you :) – Lukasz Szozda Oct 14 '15 at 14:55

score -3 · Answer 2 · answered Oct 13 '15 at 18:43

-3

Year(getdate()) - birthyear shall be enough

answered Oct 13 '15 at 18:43

Zee

830
1
9
22

2

If someone was born in December 2015, what is their age in January 2016? – D Stanley Oct 13 '15 at 18:45

How to calculate age in query?

2 Answers2