[mysql] Calculate Age in MySQL (InnoDb)

If I have a persons date of birth stored in a table in the form dd-mm-yyyy and I subtract it from the current date, what format is the date returned in?

How can I use this returned format to calculate someone's age?

This question is related to mysql sql

The answer is


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

Demo


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

Click for demo..

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