在 MySQL 中计算年龄(InnoDB)

select floor(datediff (now(), birthday)/365) as age

Use:

select *,year(curdate())-year(dob) - (right(curdate(),5) < right(dob,5)) as age from your_table

In this way, you consider even month and day of birth in order to have a more accurate age calculation.

If the value is stored as a DATETIME data type:

SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(dob) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(dob, 5)) as age
FROM YOUR_TABLE

Less precise when you consider leap years:

SELECT DATEDIFF(CURRENT_DATE, STR_TO_DATE(t.birthday, '%d-%m-%Y'))/365 AS ageInYears
FROM YOUR_TABLE t

Try this:

SET @birthday = CAST('1980-05-01' AS DATE);
SET @today = CURRENT_DATE();


SELECT YEAR(@today) - YEAR(@birthday) -
(CASE WHEN
MONTH(@birthday) > MONTH(@today) OR
(MONTH(@birthday) = MONTH(@today) AND DAY(@birthday) > DAY(@today))
THEN 1
ELSE 0
END);

It returns this year - birth year (how old the person will be this year after the birthday) and adjusts based on whether the person has had the birthday yet this year.

It doesn't suffer from the rounding errors of other methods presented here.

^{Freely adapted from here}

This is how to calculate the age in MySQL:

select
date_format(now(), '%Y') - date_format(date_of_birth, '%Y') -
(date_format(now(), '00-%m-%d') < date_format(date_of_birth, '00-%m-%d'))
as age from table

You can make a function to do it:

drop function if exists getIdade;


delimiter |


create function getIdade( data_nascimento datetime )
returns int
begin
declare idade int;
declare ano_atual int;
declare mes_atual int;
declare dia_atual int;
declare ano int;
declare mes int;
declare dia int;


set ano_atual = year(curdate());
set mes_atual = month( curdate());
set dia_atual = day( curdate());


set ano = year( data_nascimento );
set mes = month( data_nascimento );
set dia = day( data_nascimento );


set idade = ano_atual - ano;


if( mes > mes_atual ) then
set idade = idade - 1;
end if;


if( mes = mes_atual and dia > dia_atual ) then
set idade = idade - 1;
end if;


return idade;
end|


delimiter ;

Now, you can get the age from a date:

select getIdade('1983-09-16');

If you date is in format Y-m-d H:i:s, you can do this:

select getIdade(substring_index('1983-09-16 23:43:01', ' ', 1));

You can reuse this function anywhere ;)

I prefer use a function this way.

DELIMITER $$ DROP FUNCTION IF EXISTS `db`.`F_AGE` $$
CREATE FUNCTION `F_AGE`(in_dob datetime) RETURNS int(11)
NO SQL
BEGIN
DECLARE l_age INT;
IF DATE_FORMAT(NOW(  ),'00-%m-%d') >= DATE_FORMAT(in_dob,'00-%m-%d') THEN
-- This person has had a birthday this year
SET l_age=DATE_FORMAT(NOW(  ),'%Y')-DATE_FORMAT(in_dob,'%Y');
ELSE
-- Yet to have a birthday this year
SET l_age=DATE_FORMAT(NOW(  ),'%Y')-DATE_FORMAT(in_dob,'%Y')-1;
END IF;
RETURN(l_age);
END $$


DELIMITER ;

now to use

SELECT F_AGE('1979-02-11') AS AGE;

OR

SELECT F_AGE(date) AS age FROM table;

Simply:

DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(`birthDate`)), '%Y')+0 AS age

Since the question is being tagged for mysql, I have the following implementation that works for me and I hope similar alternatives would be there for other RDBMS's. Here's the sql:

select YEAR(now()) - YEAR(dob) - ( DAYOFYEAR(now()) < DAYOFYEAR(dob) ) as age
from table
where ...

You can use TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2) function:

SELECT TIMESTAMPDIFF(YEAR, '1970-02-01', CURDATE()) AS age

Demo

SELECT TIMESTAMPDIFF (YEAR, YOUR_COLUMN, CURDATE()) FROM YOUR_TABLE AS AGE

Check the demo image below

Simple but elegant..

Simply do

SELECT birthdate, (YEAR(CURDATE())-YEAR(birthdate)) AS age FROM `member`

birthdate is field name that keep birthdate name take CURDATE() turn to year by YEAR() command minus with YEAR() from the birthdate field

There is two simple ways to do that:

1.  

    select("users.birthdate",
   DB::raw("FLOOR(DATEDIFF(CURRENT_DATE, STR_TO_DATE(users.birthdate, '%Y-%m-%d'))/365) AS age_way_one"),
   

2.  

    select("users.birthdate",DB::raw("(YEAR(CURDATE())-YEAR(users.birthdate)) AS age_way_two"))