# mysql - 计算MySQL中的年龄（InnoDb）

#### 13 个答案:

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

<强> Demo

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

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

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

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

Click for demo ..

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

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

``````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);
``````

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

``````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
``````

``````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 ;
``````

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

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

``````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 ;
``````

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

OR

``````SELECT F_AGE(date) AS age FROM table;
``````

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

birthdate是保留生日名称的字段名称 采用CURDATE（）通过YEAR（）命令转到年份 减去生日字段中的YEAR（）

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"))
``````

0条回复