Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 44.7Mb
PDF (A4) - 44.7Mb
PDF (RPM) - 40.5Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.5Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 205.8Kb
Man Pages (Zip) - 309.0Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

3.3.4.5 Date Calculations

MySQL provides several functions that you can use to perform calculations on dates, for example, to calculate ages or extract parts of dates.

To determine how many years old each of your pets is, use the TIMESTAMPDIFF() function. Its arguments are the unit in which you want the result expressed, and the two dates for which to take the difference. The following query shows, for each pet, the birth date, the current date, and the age in years. An alias (age) is used to make the final output column label more meaningful.

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
+----------+------------+------------+------+

The query works, but the result could be scanned more easily if the rows were presented in some order. This can be done by adding an ORDER BY name clause to sort the output by name:

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet ORDER BY name;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
+----------+------------+------------+------+

To sort the output by age rather than name, just use a different ORDER BY clause:

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet ORDER BY age;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
+----------+------------+------------+------+

A similar query can be used to determine age at death for animals that have died. You determine which animals these are by checking whether the death value is NULL. Then, for those with non-NULL values, compute the difference between the death and birth values:

mysql> SELECT name, birth, death,
    -> TIMESTAMPDIFF(YEAR,birth,death) AS age
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 |    5 |
+--------+------------+------------+------+

The query uses death IS NOT NULL rather than death <> NULL because NULL is a special value that cannot be compared using the usual comparison operators. This is discussed later. See Section 3.3.4.6, “Working with NULL Values”.

What if you want to know which animals have birthdays next month? For this type of calculation, year and day are irrelevant; you simply want to extract the month part of the birth column. MySQL provides several functions for extracting parts of dates, such as YEAR(), MONTH(), and DAYOFMONTH(). MONTH() is the appropriate function here. To see how it works, run a simple query that displays the value of both birth and MONTH(birth):

mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+

Finding animals with birthdays in the upcoming month is also simple. Suppose that the current month is April. Then the month value is 4 and you can look for animals born in May (month 5) like this:

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

There is a small complication if the current month is December. You cannot merely add one to the month number (12) and look for animals born in month 13, because there is no such month. Instead, you look for animals born in January (month 1).

You can write the query so that it works no matter what the current month is, so that you do not have to use the number for a particular month. DATE_ADD() enables you to add a time interval to a given date. If you add a month to the value of CURDATE(), then extract the month part with MONTH(), the result produces the month in which to look for birthdays:

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

A different way to accomplish the same task is to add 1 to get the next month after the current one after using the modulo function (MOD) to wrap the month value to 0 if it is currently 12:

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

MONTH() returns a number between 1 and 12. And MOD(something,12) returns a number between 0 and 11. So the addition has to be after the MOD(), otherwise we would go from November (11) to January (1).

If a calculation uses invalid dates, the calculation fails and produces warnings:

mysql> SELECT '2018-10-31' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-31' + INTERVAL 1 DAY |
+-------------------------------+
| 2018-11-01                    |
+-------------------------------+
mysql> SELECT '2018-10-32' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-32' + INTERVAL 1 DAY |
+-------------------------------+
| NULL                          |
+-------------------------------+
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2018-10-32' |
+---------+------+----------------------------------------+

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Mark Stafford on January 6, 2011
-- working with the other examples on this page I decided to return age as a date which facilitates further parsing of the results

DELIMITER //

DROP FUNCTION IF EXISTS get_age //

CREATE FUNCTION get_age( p_after date, p_before date )
RETURNS date
BEGIN

DECLARE l_offset DATE default date_format('0001-01-01', '%Y-%m-%d');
DECLARE l_date_age DATE default date_sub( date_add( l_offset, INTERVAL datediff( p_after , p_before ) DAY ) - l_offset , interval 2000 year );

RETURN l_date_age;

END //

DELIMITER ;

  Posted by Poldi Rijke on March 8, 2011
MySQl 5.0 or higher: TIMESTAMPDIFF

SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE() ) AS 'age in years'
FROM person;

From MySQL Cookbook p248
  Posted by Rahul Sonar on May 16, 2011
SELECT CURDATE(),
(YEAR(CURDATE())- YEAR(dob))
- (
RIGHT(CURDATE(),5)<
RIGHT(dob,5)) AS age,
case when ((MONTH(CURDATE())- MONTH(dob))
- (RIGHT(CURDATE(),2)<RIGHT(dob,2)))>0 then ((MONTH(CURDATE())- MONTH(dob))
- (RIGHT(CURDATE(),2)<RIGHT(dob,2))) else ((MONTH(CURDATE())- MONTH(dob))
- (RIGHT(CURDATE(),2)<RIGHT(dob,2))) + 12 end AS Months,

(DAY(CURDATE() - DAY(dob))) AS Days;
  Posted by Soumen Das on December 9, 2011
BIRTH DAY

SELECT emp_name,email FROM employee where DATE_FORMAT(now(),'%m')-DATE_FORMAT(emp_dob,'%m')=0 and DATE_FORMAT(now(),'%d')-DATE_FORMAT(emp_dob,'%d')=0

UPCOMING BIRTHDAY

SELECT emp_name,email,DATE_FORMAT(emp_dob,'%d-%M') as bdt FROM employee where DATE_FORMAT(now(),'%m')-DATE_FORMAT(emp_dob,'%m')=0 and DATE_FORMAT(now(),'%d')-DATE_FORMAT(emp_dob,'%d')<0
  Posted by Thomas Pepe on March 9, 2012
MySQL noob here. To get all pets of a certain age (like 5 years old or more) I think this would be more efficient. Please let me know if I am mistaken.

##5 year old pets
DECLARE fiveYearsAgo = CURDATE() - INTERVAL 5 YEAR;
SELECT name, birth
FROM pet
WHERE pet.birth <= fiveYearsAgo
;

##5 years only
DECLARE fourYearsAgo = CURDATE() - INTERVAL 4 YEAR;
SELECT name, birth
FROM pet
WHERE pet.birth BETWEEN fiveYearsAgo AND fourYearsAgo
;

##NOTE: To consider time I could truncate the time off of the five year date and add to the last nanosecond of the four year date

Thank you.
  Posted by Uchechukwu Chikezie on October 30, 2014
So from the lesson, I can infer that the query below will select age in terms of years and months:

SELECT birth_date,
CONCAT(TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE()), ' years ', TIMESTAMPDIFF(MONTH, CONCAT(YEAR(CURRENT_DATE()), '-',MONTH(birth_date), '-', DAY(birth_date)), CURRENT_DATE()), ' months ') as currentage,
CONCAT(YEAR(CURRENT_DATE()), '-',MONTH(birth_date), '-', DAY(birth_date)) AS thisyearsbirthday,
CURRENT_DATE() AS today
FROM pet
  Posted by Gauravkumar Mishra on February 26, 2015
I had a similar problem when I was requested to calculate age of Patients in Years, Months and Days. I did not find any solution and had to create the below for my rescue. I hope this helps everyone, until we have a inbuilt function which calculates age accurately in Years, Months and Days.

select CONCAT(
CASE WHEN DAY(dob) > DAY(CURDATE()) AND (MONTH(dob) > MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 1) OR (MONTH(CURDATE()) = 3) OR (MONTH(CURDATE()) = 5) OR (MONTH(CURDATE()) = 7) OR (MONTH(CURDATE()) = 8) OR (MONTH(CURDATE()) = 10) OR (MONTH(CURDATE()) = 12))
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 12 - (MONTH(dob) - MONTH(CURDATE())) - 1 ,' Month ',30 - (DAY(dob) - DAY(CURDATE())) ,' Day ')
)
WHEN DAY(dob) > DAY(CURDATE()) AND (MONTH(dob) > MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 4) OR (MONTH(CURDATE()) = 6) OR (MONTH(CURDATE()) = 9) OR (MONTH(CURDATE()) = 11))
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 12 - (MONTH(dob) - MONTH(CURDATE())) - 1 ,' Month ',31 - (DAY(dob) - DAY(CURDATE())) ,' Day ')
)
WHEN DAY(dob) > DAY(CURDATE()) AND (MONTH(dob) > MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) = 0)
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 12 - (MONTH(dob) - MONTH(CURDATE())) - 1 ,' Month ',31 - (DAY(dob) - DAY(CURDATE())) ,' Day ')
)
WHEN DAY(dob) > DAY(CURDATE()) AND (MONTH(dob) > MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) != 0)
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 12 - (MONTH(dob) - MONTH(CURDATE())) - 1 ,' Month ',31 - (DAY(dob) - DAY(CURDATE())) ,' Day ')
)
WHEN DAY(dob) > DAY(CURDATE()) AND (MONTH(dob) = MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 1) OR (MONTH(CURDATE()) = 3) OR (MONTH(CURDATE()) = 5) OR (MONTH(CURDATE()) = 7) OR (MONTH(CURDATE()) = 8) OR (MONTH(CURDATE()) = 10) OR (MONTH(CURDATE()) = 12))
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 11 ,' Month ',3 - (DAY(dob) - DAY(CURDATE())) ,' Day ')
)
WHEN DAY(dob) > DAY(CURDATE()) AND (MONTH(dob) = MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 4) OR (MONTH(CURDATE()) = 6) OR (MONTH(CURDATE()) = 9) OR (MONTH(CURDATE()) = 11))
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 11 ,' Month ',31 - (DAY(dob) - DAY(CURDATE())) ,' Day ')
)
WHEN DAY(dob) > DAY(CURDATE()) AND (MONTH(dob) = MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) = 0)
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 11 ,' Month ',31 - (DAY(dob) - DAY(CURDATE())) ,' Day ')
)
WHEN DAY(dob) > DAY(CURDATE()) AND (MONTH(dob) = MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) != 0)
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 11 ,' Month ',31 - (DAY(dob) - DAY(CURDATE())) ,' Day ')
)
WHEN DAY(dob) > DAY(CURDATE()) AND (MONTH(dob) < MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 1) OR (MONTH(CURDATE()) = 3) OR (MONTH(CURDATE()) = 5) OR (MONTH(CURDATE()) = 7) OR (MONTH(CURDATE()) = 8) OR (MONTH(CURDATE()) = 10) OR (MONTH(CURDATE()) = 12))
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', 0 ,' Month ',30 - (DAY(dob) - DAY(CURDATE())) ,' Day ')
)
WHEN DAY(dob) > DAY(CURDATE()) AND (MONTH(dob) < MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 4) OR (MONTH(CURDATE()) = 6) OR (MONTH(CURDATE()) = 9) OR (MONTH(CURDATE()) = 11))
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', 0 ,' Month ',31 - (DAY(dob) - DAY(CURDATE())) ,' Day ')
)
WHEN DAY(dob) > DAY(CURDATE()) AND (MONTH(dob) < MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) = 0)
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', 0 ,' Month ',31 - (DAY(dob) - DAY(CURDATE())) ,' Day ')
)
WHEN DAY(dob) > DAY(CURDATE()) AND (MONTH(dob) < MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) != 0)
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', 0 ,' Month ',31 - (DAY(dob) - DAY(CURDATE())) ,' Day ')
)
WHEN DAY(dob) = DAY(CURDATE()) AND (MONTH(dob) > MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 1) OR (MONTH(CURDATE()) = 3) OR (MONTH(CURDATE()) = 5) OR (MONTH(CURDATE()) = 7) OR (MONTH(CURDATE()) = 8) OR (MONTH(CURDATE()) = 10) OR (MONTH(CURDATE()) = 12))
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 12 - (MONTH(dob) - MONTH(CURDATE())) ,' Month ', 0 ,' Day ')
)
WHEN DAY(dob) = DAY(CURDATE()) AND (MONTH(dob) > MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 4) OR (MONTH(CURDATE()) = 6) OR (MONTH(CURDATE()) = 9) OR (MONTH(CURDATE()) = 11))
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 12 - (MONTH(dob) - MONTH(CURDATE())) ,' Month ', 0 ,' Day ')
)
WHEN DAY(dob) = DAY(CURDATE()) AND (MONTH(dob) > MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) = 0)
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 12 - (MONTH(dob) - MONTH(CURDATE())) ,' Month ', 0 ,' Day ')
)
WHEN DAY(dob) = DAY(CURDATE()) AND (MONTH(dob) > MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) != 0)
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 12 - (MONTH(dob) - MONTH(CURDATE())) ,' Month ', 0 ,' Day ')
)
WHEN DAY(dob) = DAY(CURDATE()) AND (MONTH(dob) = MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 1) OR (MONTH(CURDATE()) = 3) OR (MONTH(CURDATE()) = 5) OR (MONTH(CURDATE()) = 7) OR (MONTH(CURDATE()) = 8) OR (MONTH(CURDATE()) = 10) OR (MONTH(CURDATE()) = 12))
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', 0 ,' Month ', 0 ,' Day ')
)
WHEN DAY(dob) = DAY(CURDATE()) AND (MONTH(dob) = MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 4) OR (MONTH(CURDATE()) = 6) OR (MONTH(CURDATE()) = 9) OR (MONTH(CURDATE()) = 11))
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', 0 ,' Month ', 0 ,' Day ')
)
WHEN DAY(dob) = DAY(CURDATE()) AND (MONTH(dob) = MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) = 0)
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', 0 ,' Month ', 0 ,' Day ')
)
WHEN DAY(dob) = DAY(CURDATE()) AND (MONTH(dob) = MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) != 0)
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', 0 ,' Month ', 0 ,' Day ')
)
WHEN DAY(dob) = DAY(CURDATE()) AND (MONTH(dob) < MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 1) OR (MONTH(CURDATE()) = 3) OR (MONTH(CURDATE()) = 5) OR (MONTH(CURDATE()) = 7) OR (MONTH(CURDATE()) = 8) OR (MONTH(CURDATE()) = 10) OR (MONTH(CURDATE()) = 12))
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', MONTH(CURDATE()) - MONTH(dob) ,' Month ', 0 ,' Day ')
)
WHEN DAY(dob) = DAY(CURDATE()) AND (MONTH(dob) < MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 4) OR (MONTH(CURDATE()) = 6) OR (MONTH(CURDATE()) = 9) OR (MONTH(CURDATE()) = 11))
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', MONTH(CURDATE()) - MONTH(dob) ,' Month ', 0 ,' Day ')
)
WHEN DAY(dob) = DAY(CURDATE()) AND (MONTH(dob) < MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) = 0)
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', MONTH(CURDATE()) - MONTH(dob) ,' Month ', 0 ,' Day ')
)
WHEN DAY(dob) = DAY(CURDATE()) AND (MONTH(dob) < MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) != 0)
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', MONTH(CURDATE()) - MONTH(dob) ,' Month ', 0 ,' Day ')
)
WHEN DAY(dob) < DAY(CURDATE()) AND (MONTH(dob) > MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 1) OR (MONTH(CURDATE()) = 3) OR (MONTH(CURDATE()) = 5) OR (MONTH(CURDATE()) = 7) OR (MONTH(CURDATE()) = 8) OR (MONTH(CURDATE()) = 10) OR (MONTH(CURDATE()) = 12))
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 12 - MONTH(dob) + MONTH(CURDATE()) ,' Month ', DAY(CURDATE()) - DAY(dob) ,' Day ')
)
WHEN DAY(dob) < DAY(CURDATE()) AND (MONTH(dob) > MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 4) OR (MONTH(CURDATE()) = 6) OR (MONTH(CURDATE()) = 9) OR (MONTH(CURDATE()) = 11))
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 12 - MONTH(dob) + MONTH(CURDATE()) ,' Month ', DAY(CURDATE()) - DAY(dob) ,' Day ')
)
WHEN DAY(dob) < DAY(CURDATE()) AND (MONTH(dob) > MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) = 0)
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 12 - MONTH(dob) + MONTH(CURDATE()) ,' Month ', DAY(CURDATE()) - DAY(dob) ,' Day ')
)
WHEN DAY(dob) < DAY(CURDATE()) AND (MONTH(dob) > MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) != 0)
THEN(
CONCAT(YEAR(CURDATE()) - 1 - YEAR(dob), ' Year ', 12 - MONTH(dob) + MONTH(CURDATE()) ,' Month ', DAY(CURDATE()) - DAY(dob) ,' Day ')
)
WHEN DAY(dob) < DAY(CURDATE()) AND (MONTH(dob) = MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 1) OR (MONTH(CURDATE()) = 3) OR (MONTH(CURDATE()) = 5) OR (MONTH(CURDATE()) = 7) OR (MONTH(CURDATE()) = 8) OR (MONTH(CURDATE()) = 10) OR (MONTH(CURDATE()) = 12))
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', 0 ,' Month ', DAY(CURDATE()) - DAY(dob) ,' Day ')
)
WHEN DAY(dob) < DAY(CURDATE()) AND (MONTH(dob) = MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 4) OR (MONTH(CURDATE()) = 6) OR (MONTH(CURDATE()) = 9) OR (MONTH(CURDATE()) = 11))
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', 0 ,' Month ', DAY(CURDATE()) - DAY(dob) ,' Day ')
)
WHEN DAY(dob) < DAY(CURDATE()) AND (MONTH(dob) = MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) = 0)
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', 0 ,' Month ', DAY(CURDATE()) - DAY(dob) ,' Day ')
)
WHEN DAY(dob) < DAY(CURDATE()) AND (MONTH(dob) = MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) != 0)
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', 0 ,' Month ', DAY(CURDATE()) - DAY(dob) ,' Day ')
)
WHEN DAY(dob) < DAY(CURDATE()) AND (MONTH(dob) < MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 1) OR (MONTH(CURDATE()) = 3) OR (MONTH(CURDATE()) = 5) OR (MONTH(CURDATE()) = 7) OR (MONTH(CURDATE()) = 8) OR (MONTH(CURDATE()) = 10) OR (MONTH(CURDATE()) = 12))
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', MONTH(CURDATE()) - MONTH(dob) ,' Month ', DAY(CURDATE()) - DAY(dob) ,' Day ')
)
WHEN DAY(dob) < DAY(CURDATE()) AND (MONTH(dob) < MONTH(CURDATE())) AND ((MONTH(CURDATE()) = 4) OR (MONTH(CURDATE()) = 6) OR (MONTH(CURDATE()) = 9) OR (MONTH(CURDATE()) = 11))
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', MONTH(CURDATE()) - MONTH(dob) ,' Month ', DAY(CURDATE()) - DAY(dob) ,' Day ')
)
WHEN DAY(dob) < DAY(CURDATE()) AND (MONTH(dob) < MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) = 0)
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', MONTH(CURDATE()) - MONTH(dob) ,' Month ', DAY(CURDATE()) - DAY(dob) ,' Day ')
)
WHEN DAY(dob) < DAY(CURDATE()) AND (MONTH(dob) < MONTH(CURDATE())) AND (MONTH(CURDATE()) = 2) AND (MOD(YEAR(CURDATE()),4) != 0)
THEN(
CONCAT(YEAR(CURDATE()) - YEAR(dob), ' Year ', MONTH(CURDATE()) - MONTH(dob) ,' Month ', DAY(CURDATE()) - DAY(dob) ,' Day ')
)
END
) as "Age" from Patient_Details;
  Posted by Rob Bartram on May 27, 2015
Using Gauravkumar Mishra's method for Years, Months, Days, I was getting -1 days for some dates.

Here is what I ended up with: (sorry, I'm not sure how to make it more readable)

SELECT (CONCAT(CONCAT(CONCAT(TIMESTAMPDIFF(YEAR, dob, CURDATE()), ' years, ')
,CONCAT(TIMESTAMPDIFF(MONTH, dob, CURDATE()) - ((TIMESTAMPDIFF(MONTH, dob, CURDATE()) div 12) * 12), ' months, '))
,CONCAT(TIMESTAMPDIFF(DAY, DATE_ADD(DATE_ADD(dob,INTERVAL TIMESTAMPDIFF(YEAR, dob, CURDATE()) YEAR), INTERVAL TIMESTAMPDIFF(MONTH, dob, CURDATE()) -
((TIMESTAMPDIFF(MONTH, dob, CURDATE()) div 12) * 12) MONTH), CURDATE()), ' days')) as "Age" from Patient_Details;
Sign Up Login You must be logged in to post a comment.