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.
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
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 ;)
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
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
SELECT TIMESTAMPDIFF (YEAR, YOUR_COLUMN, CURDATE()) FROM YOUR_TABLE AS AGE
Simple but elegant..
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
There is two simples 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"))
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
select floor(datediff (now(), birthday)/365) as age
Source: Stackoverflow.com