How to get an age from a D.O.B field in MySQL?_问答_开发者

I need to calculate the age of a "customer" from their date of birth.

I have tried to use the following:

DATEDIFF(year, customer.dob, "2010-01-01");

But it does not seem to work.

Any ideas? I KNOW it is going to be something simple!

Thanks

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age

Use Mysql recommended :

TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age;

Usage in a query :

SELECT name, dob, TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age FROM pet;

Ref: http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

A few ways:

select DATEDIFF(customer.dob, '2010-01-01') / 365.25 as age

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(customer.dob,'2010-01-01')), ‘%Y’)+0 AS age

Hope this helps you

Bryan Denny's answer is more correct than the accepted answer (I wasn't sure how to put this in somewhere other than a new answer; this is my first time on StackOverflow).

Marcos' first attempt:

select DATEDIFF(customer.dob, '2010-01-01') / 365.25 as age

will firstly yield a negative result (the arguments to DATEDIFF are in the wrong order), and secondly will produce inaccurate results for some dates, e.g.:

SELECT DATEDIFF('2010-05-11','1984-05-11') / 365.25 AS age

produces the result:

25.9986

You can't just simply always round up, because that will also cause inaccurate results for other inputs.

Marcos' second attempt:

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(customer.dob,'2010-01-01')), ‘%Y’)+0 AS age

Again, the arguments are in the wrong order, except this time instead of just producing a negative number, the FROM_DAYS() function does not work correctly with negative input. Secondly, if we look closer at the output of the FROM_DAYS() function:

select from_days(datediff('2010-09-16','1984-05-11'));

The result of the above is:

0026-05-08

which is literally "8th of May, Year 26 (after 0)". Keep in mind that for datetime types, there is no month "0", so if you wanted to use this format to measure a date interval with months included, you'd have to subtract 1 from the month. Similarly, with the day component, there is no "0", so the result is not what you'd expect for this problem when the date happens to be the birthday:

select from_days(datediff('2010-05-11','1984-05-11'));

produces:

0025-12-31

which if we shorten using Marcos' date formatting gives us "25", which is an incorrect calculation of age.

Bryan Denny's answer is correct in all these edge cases. His formula is quite clever:

SELECT DATE_FORMAT(reference, '%Y') - DATE_FORMAT(birthdate, '%Y') - (DATE_FORMAT(reference, '00-%m-%d') < DATE_FORMAT(birthdate, '00-%m-%d')) AS age

The first part calculates the difference in years between the two dates. So if we take "2010" and "1984" as reference and birthdate respectively, the result is "26". The second part then calculates essentially "Does the birthdate month and day occur after the reference month and day?" If it does, it "hasn't happened yet", so we need to subtract an additional 1 from the year difference to make up for this. This is taken care of by the result of the < comparison, which returns 1 if true and 0 if false.

So, full examples:

Reference date: 2010-05-10;
Birthdate: 1984-05-11

Year difference = 2010 - 1984 = 26
Month and day comparison: May 10th < May 11th? Yes => subtract an additional year
Calculated age: 25 years

Reference date: 2010-05-11;
Birthdate: 1984-05-11

Year difference = 2010 - 1984 = 26
Month and day comparison: May 11th < May 11th? No => subtract 0
Calculated age: 26 years

I hope this makes things clearer for people!

The below sql works fine for me. Always use CURRENT_DATE with dob to calculate the actual age .

SELECT 
    DATE_FORMAT(
        FROM_DAYS(
            DATEDIFF(CURRENT_DATE, dob)
        ),
        '%y Years %m Months %d Days'
    ) AS age 
FROM 
    users

The easy way:

timestampdiff( YEAR, birth_date, now() ) AS age

SELECT *, YEAR(CURDATE()) - YEAR(birthdate) AS age FROM user;

I found some useful queries from below article for calculating age :- http://www.gizmola.com/blog/archives/archives/107-Calculate-a-persons-age-in-a-MySQL-query.html

DELIMITER $$ DROP FUNCTION IF EXISTS `test`.`_AGE` $$
    CREATE FUNCTION `_AGE`(in_dob datetime) RETURNS VARCHAR(100)
        NO SQL
    BEGIN
       DECLARE l_age VARCHAR(100);
       DECLARE YEARS INT(11);
       DECLARE MONTHS INT(11);
       DECLARE DAYS INT(11);
       DECLARE DIFFS FLOAT;


       SET DIFFS=DATEDIFF(CURRENT_DATE(),in_dob) /365.25;
       SET YEARS=FLOOR(DIFFS) ;
       SET MONTHS=FLOOR((DIFFS - YEARS)*365.25/30.4375) MOD 12;
       SET DIFFS=((DIFFS - YEARS)*365.25/30.4375);
       SET DAYS=CEIL(((DIFFS-MONTHS)*30.4375)) MOD 31;
       SET l_age=CONCAT(YEARS, " Year ",MONTHS," Month ",DAYS," Days");
       RETURN(l_age);
    END $$
DELIMITER ;

SELECT _Age(CAST('1980-07-16' AS DATE));

DATE_FORMAT(FROM_DAYS(DATEDIFF(CURDATE(),'1869-10-02')), '%Y')+0 AS age;

Above MySQL query has been tested and verified. It will give you exact age in years. I have taken this idea from Marcos answer and switched DATEDIFF() parameters.

If you want years, months and days like "26 years 4 months and 27 days" then following is the age function for you:

delimiter $$

create function age(dob date)
returns varchar(30)
deterministic
begin
declare years int default 0;
declare months int default 0;
declare days int default 0;
declare today date default curdate();

set years = timestampdiff(year, dob, today);    
set months = timestampdiff(month, dob, today - interval years year);    
set days = timestampdiff(day, dob, today - interval years year - interval months month);

return (concat(years, ' years ', months,' months and ', days,' days'));

end$$

delimiter;

We are calculating the age from current date so I set today to curdate(). Now we are getting our years, months and days as following.

years: We get the difference in years from today to date of birth.
months: First we subtract the years from the today so that we only get months that are extra from the years and then find the difference in months from date of birth. Here "years" is the variable and "year" is interval type.
days: And here we subtract years and months that we have already calculated from today which is current date then find the diff in days from birth. Here "months" is the variable and "month" is interval type.

You can use datetime instead of date as argument if you want also you can format the return values as you like.

DROP FUNCTION IF EXISTS FN_GetDuration;
CREATE FUNCTION `FN_GetDuration`(p_STARTDATE DATETIME, p_ENDDATE DATETIME) RETURNS varchar(200) CHARSET utf8
    DETERMINISTIC
BEGIN
DECLARE v_DAYS INT; 
DECLARE v_MONTHS INT; 
DECLARE v_YEARS INT;
DECLARE v_BASEMONTH INT; 
DECLARE v_TOTALDAYS INT;
DECLARE v_STYRMONTHDAYS INT; 
DECLARE v_ENYRMONTHDAYS INT; 
DECLARE v_STYRMONTHS INT; 
DECLARE v_ENYRMONTHS INT;
DECLARE v_DATESTRING VARCHAR(50); 
DECLARE v_FROMDATE DATETIME; 
DECLARE v_TODATE DATETIME;
DECLARE v_ENDMONTH INT(10); 
DECLARE v_STARTMONTH INT(10);
DECLARE v_TempValue Varchar(10);
DECLARE v_Year Varchar(2);
DECLARE v_Month Varchar(2);
DECLARE v_Day Varchar(2);
SET v_BASEMONTH = 12; SET v_DAYS = 0; SET v_MONTHS=0; SET v_YEARS=0;
IF p_STARTDATE <= p_ENDDATE
THEN
    IF p_STARTDATE <> p_ENDDATE
    THEN
        
        SET v_TOTALDAYS = FN_TotalDaysOfMonth(p_STARTDATE);
        
        SET v_STYRMONTHDAYS = v_TOTALDAYS - DAY(p_STARTDATE) + 1;
        
        SET v_ENYRMONTHDAYS = DAY(p_ENDDATE);
        SET v_DAYS = v_STYRMONTHDAYS + v_ENYRMONTHDAYS;
    END IF;
    
    IF p_STARTDATE = p_ENDDATE
    THEN
        SET v_DAYS = 1;
    END IF;
    
    IF v_DAYS > v_TOTALDAYS
    THEN
        SET v_MONTHS=1;
        SET v_DAYS = v_DAYS-v_TOTALDAYS;
    END IF;
    
    
    IF YEAR(p_STARTDATE) <> YEAR(p_ENDDATE)
        THEN
            SET v_STYRMONTHS = v_BASEMONTH - MONTH(p_STARTDATE);
            SET v_ENYRMONTHS = MONTH(p_ENDDATE)-1;
            SET v_MONTHS = v_MONTHS + v_STYRMONTHS + v_ENYRMONTHS;
      SET v_DATESTRING = CONCAT(((YEAR(p_STARTDATE))+1 ) , '-01-01 ');
      SET v_FROMDATE = v_DATESTRING;

            SET v_DATESTRING = CONCAT(((YEAR(p_ENDDATE))-1 ) , '-12-31 ');
      SET v_TODATE = v_DATESTRING;

            IF YEAR(v_TODATE)<> YEAR(v_FROMDATE)
                THEN
                    SET v_YEARS = (YEAR(v_TODATE)- YEAR(v_FROMDATE))+1;
            ELSE
                    SET v_YEARS = 1;
            END IF;

            IF v_MONTHS >= v_BASEMONTH
                THEN
                    SET v_YEARS = v_YEARS + 1;
                    SET v_MONTHS = v_MONTHS - v_BASEMONTH;
                END IF;
    ELSE
            SET v_ENDMONTH = MONTH(p_ENDDATE);
            SET v_STARTMONTH = MONTH(p_STARTDATE);
            
            IF p_STARTDATE <> p_ENDDATE
            THEN
                SET v_MONTHS = v_MONTHS + ((v_ENDMONTH - v_STARTMONTH)-1);
            END IF;
        END IF;
END IF;
    IF v_YEARS<10 THEN
        SET v_Year=CONCAT('0', v_YEARS);
    ELSE
        SET v_Year= v_YEARS;
    END IF;
    
    IF v_MONTHS<10 THEN
        SET v_Month= CONCAT('0', v_MONTHS);
    ELSE
        SET v_Month= v_MONTHS;
    END IF;
    IF v_DAYS<10 THEN
        SET v_Day= CONCAT('0',v_DAYS);
    ELSE
        SET v_Day= v_DAYS;
    END IF;
  SET v_TempValue = CONCAT(v_Year, '-', v_Month, '-', v_Day);
RETURN v_TempValue;
END;

This is the simplest I could come up with so far:

SELECT FLOOR(ABS(DATEDIFF(d, CURRENT_TIMESTAMP, dob))/365.25) AS age

First we get the date difference in days, then convert it to years, then FLOOR truncates to the integer part of the number.

Depends on your needs - int and float functions provided.
- Your business rules may differ so adjust accordingly

DROP FUNCTION IF EXISTS `age`; 
CREATE FUNCTION `age` (
  `pdate_begin` DATE,
  `pdate_end` DATETIME
) RETURNS INT(11) UNSIGNED   
COMMENT 'Calc age between two dates as INT' 
DETERMINISTIC NO SQL SQL SECURITY DEFINER
RETURN floor(datediff(pdate_end, pdate_begin) / 365.25) ;

DROP FUNCTION IF EXISTS `age_strict`; 
CREATE FUNCTION `age_strict` (
  `pdate_begin` DATE,
  `pdate_end` DATETIME
) RETURNS decimal(10,4)
COMMENT 'Calc age between two dates as DECIMAL .4' 
DETERMINISTIC NO SQL SQL SECURITY DEFINER
RETURN round(datediff(pdate_end, pdate_begin) / 365.25, 4) ;

-- test harness
select 
    age(dob, now())        as age_int,
    age_strict(dob, now()) as age_dec
    from customer 
    where dob is not null 
    order by age(dob,now()) desc;

-- test results
dob,                  age_int,            age_dec
1981-01-01 00:00:00        33             33.9713
1987-01-09 00:00:00        27             27.9507
2014-11-25 00:00:00         0              0.0739

DELIMITER $$ DROP FUNCTION IF EXISTS `test`.`__AGE` $$
    CREATE FUNCTION `_AGE`(in_dob datetime) RETURNS VARCHAR(100)
        NO SQL
    BEGIN
       DECLARE l_age VARCHAR(100);
       DECLARE YEARS INT(11);
       DECLARE MONTHS INT(11);
       DECLARE DAYS INT(11);
       DECLARE DIFFS FLOAT;


       SET DIFFS=DATEDIFF(CURRENT_DATE(),in_dob) /365.25;
       SET YEARS=FLOOR(DIFFS) ;
       SET MONTHS=FLOOR((DIFFS - YEARS)*365.25/30.4375) MOD 12;
       SET DIFFS=((DIFFS - YEARS)*365.25/30.4375);
       SET DAYS=CEIL(((DIFFS-MONTHS)*30.4375)) MOD 31;
       RETURN(CONCAT(YEARS, " Year ",MONTHS," Month ",DAYS," Days"));

    END $$
DELIMITER ;

SELECT __Age(CAST('1980-07-16' AS DATE));

Assumed the given date is greater than the current date,

1.Find the total no of days b/w the current date and the given date.

-> DATEDIFF(NOW(),'1988-05-01')

2.Find the no of years from the calculated no of days.

-> DATEDIFF(NOW(),'1988-05-01')/365.25

3.The age should be the completed no of years by a person. To get it, we can use 'floor' to the calculated no of years.

-> FLOOR(DATEDIFF(NOW(),'1988-05-01')/365.25)

Example: SELECT FLOOR(DATEDIFF(NOW(),'1988-05-01')/365.25) AS age;