MySQL Date Age Calculation

I have always had the need to calculate the age as year and month from a MySQL field of type date. No doubt you can achieve this with a bit of PHP, but when you require to do the conversion right from MySQL, you can only go as far as getting the age in “years”.

I have used here a MySQL stored routine to achive this:

DELIMITER $$
DROP FUNCTION IF EXISTS `my_database`.`getage` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getage`(pdate DATE) RETURNS char(30) DETERMINISTIC
BEGIN
DECLARE years INT;
DECLARE months INT;
DECLARE days INT;
 
DECLARE current_year INT;
DECLARE current_month INT;
DECLARE current_day INT;
 
DECLARE year_diff INT;
DECLARE month_diff INT;
DECLARE day_diff INT;
 
DECLARE yearstring CHAR(6);
DECLARE monthstring CHAR(7);
 
SELECT YEAR(pdate) INTO years;
SELECT MONTH(pdate) INTO months;
SELECT DAY(pdate) INTO days;
 
SELECT YEAR(CURRENT_DATE()) INTO current_year;
SELECT MONTH(CURRENT_DATE()) INTO current_month;
SELECT DAY(CURRENT_DATE()) INTO current_day;
 
SELECT (current_year - years) INTO year_diff;
SELECT (current_month - months) INTO month_diff;
SELECT (current_day - days) INTO day_diff;
 
if (current_month < months) THEN
	SET month_diff = (((months - 12) * -1) + current_month);
	SET year_diff = (year_diff - 1);
END IF;
 
if ( month_diff = 1 ) THEN
	SET monthstring = "month";
ELSE
	SET monthstring = "months";
END IF;
 
if ( year_diff = 1 ) THEN
	SET yearstring = "year";
ELSE
	SET yearstring = "years";
END IF;
 
if ( year_diff = 0 ) THEN
	RETURN CONCAT_WS(' ', month_diff, monthstring);
ELSE
	if (month_diff > 0) THEN
		RETURN CONCAT_WS(' ',year_diff, yearstring, month_diff, monthstring);
	ELSE
		RETURN CONCAT_WS(' ',year_diff, yearstring);
	END IF;
END IF;
 
END $$
 
DELIMITER ;

You can then make use of the stored routine to convert Dates into ages by doing so:

SELECT getage(BirthDate) as UserAge FROM my_database.users
This entry was posted in MySql and tagged , . Bookmark the permalink.

2 Responses to MySQL Date Age Calculation

  1. Allan says:

    I had a similar requirement and started down the same path as your function when it occurred to me that I can simplify everything by add the datediff to 0000-00-00; however, since that not a legal date I had to use 0001-01-01 and subtract off 1 from each field. See below:

    DELIMITER $$
     
    DROP FUNCTION IF EXISTS Get_Age $$
     
    CREATE FUNCTION Get_Age( dob DATE )
    RETURNS CHAR(20)
    BEGIN
        DECLARE years INT default 0;
        DECLARE months INT default 0;
        DECLARE days INT default 0;
        DECLARE age DATE;
     
        SELECT date_add('0001-01-01', interval datediff(current_date(),dob) day ) INTO age;
     
        -- Check that the age we're given is useful
        IF age is null or age = 0 or age = '0000-00-00' THEN
            RETURN age;
        END IF;
     
        SELECT YEAR(age) - 1 INTO years;
        SELECT MONTH(age)- 1 INTO months;
        SELECT DAY(age) - 1 INTO days;
     
        IF years THEN
            RETURN concat(years,'y ',months,'m');
     
        ELSEIF months THEN
            RETURN concat(months,'m ',days,'d');
     
        ELSE
                RETURN concat(days,' days');
        END IF;
     
    END $$
     
    DELIMITER ;
  2. Marco Anelli says:

    I think: and problem of leap year? DOB of 29 feb don’t select.???

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>