Calculate Age in MySQL InnoDb

Question

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

User · Answer

Try this   SET  birthday   CAST  1980-05-01  AS DATE   SET  today   CURRENT DATE     SELECT YEAR  today  - YEAR  birthday  -     CASE WHEN     MONTH  birthday   gt  MONTH  today  OR       MONTH  birthday    MONTH  today  AND DAY  birthday   gt  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

User · Answer

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    lt  date format date of birth   00- m- d    as age from table

User · Answer

Simply   DATE FORMAT FROM DAYS TO DAYS NOW   -TO DAYS  birthDate       Y   0 AS age

User · Answer

If the value is stored as a DATETIME data type   SELECT YEAR CURRENT TIMESTAMP  - YEAR dob  -  RIGHT CURRENT TIMESTAMP  5   lt  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

User · Answer

select   year curdate   -year dob  -  right curdate   5   lt  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

User · Answer

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

User · Answer

SELECT TIMESTAMPDIFF  YEAR  YOUR COLUMN  CURDATE    FROM YOUR TABLE AS AGE   Click for demo    Simple but elegant

User · Answer

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

User · Answer

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  gt  mes atual   then             set idade   idade - 1      end if       if  mes   mes atual and dia  gt  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

User · Answer

select floor datediff  now    birthday  365  as age

User · Answer

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

User · Answer

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     lt  DAYOFYEAR dob    as age  from table  where

User · Answer

You can use TIMESTAMPDIFF unit  datetime expr1  datetime expr2  function   SELECT TIMESTAMPDIFF YEAR   1970-02-01   CURDATE    AS age   Demo

[mysql] Calculate Age in MySQL (InnoDb)

Examples related to mysql

Examples related to sql