CODEWITHSUNDEEP
mysqlsql
user559142
user559142

Reputation: 12517

Calculate age in MySQL (InnoDB)

If I have a person's 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?

Upvotes: 98

Views: 188839

Answers (13)

Aouidane Med Amine
Aouidane Med Amine

Reputation: 1681

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

Upvotes: 0

Nicola Cossu
Nicola Cossu

Reputation: 56357

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.

Upvotes: 14

Ferd Shinoda
Ferd Shinoda

Reputation: 159

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

Check the demo image below

sql time difference example

Simple but elegant..

Upvotes: 15

Kuzmiraun
Kuzmiraun

Reputation: 27

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

Upvotes: 1

Glavić
Glavić

Reputation: 43552

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

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

Demo

Upvotes: 318

OMG Ponies
OMG Ponies

Reputation: 332531

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

Upvotes: 69

mickeymoon
mickeymoon

Reputation: 4977

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

Upvotes: 3

Damonsson
Damonsson

Reputation: 1532

Simply:

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

Upvotes: 2

HEMM
HEMM

Reputation: 11

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;

Upvotes: 0

Idealmind
Idealmind

Reputation: 1298

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

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

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

Upvotes: 0

Sklivvz
Sklivvz

Reputation: 31133

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

Upvotes: 1

lobster1234
lobster1234

Reputation: 7779

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

Upvotes: 6

Related Questions