Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 26.8Mb
PDF (A4) - 26.9Mb
HTML Download (TGZ) - 7.1Mb
HTML Download (Zip) - 7.2Mb


3.3.4.5 日付の計算

MySQL には、年齢の計算や日付の一部の抽出など、日付の計算に使用できる関数がいくつか用意されています。

それぞれのペットが何歳なのかを判別するには、TIMESTAMPDIFF() 関数を使用します。引数は、結果を表現する単位、および差を求める 2 つの日付です。次のクエリーでは、各ペットの生年月日、現在の日付、および年齢が表示されます。出力の最後のカラムラベルに意味を持たせるために、エイリアス (age) が使用されています。

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

このクエリーは正しく動作しますが、結果の行を何らかの順序で表示すると確認しやすくなるでしょう。そのためには、ORDER 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 |
+----------+------------+------------+------+

出力を name ではなく age でソートするには、異なる ORDER BY 句を使用します。

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

類似のクエリーを使用して、死んだペットの死亡時の年齢を求めることができます。どのペットなのかを判断するには、death 値が NULL かどうかを確認します。次に、NULL でない値について、death 値と birth 値の差を計算します。

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

このクエリーでは、death <> NULL ではなく death IS NOT NULL を使用します。NULL は通常の比較演算子を使用して比較することができない特殊な値であるためです。これについてはあとで説明します。セクション3.3.4.6「NULL 値の操作」を参照してください。

来月誕生日を迎えるペットを調べるにはどうしますか。このような計算の場合、年と日は無関係で、birth カラムの月の部分を抽出するだけで済みます。MySQL には、YEAR()MONTH()DAYOFMONTH() など、日付の一部を抽出する関数がいくつか用意されています。ここでは MONTH() 関数が適しています。動作の仕組みを確認するために、birthMONTH(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 |
+----------+------------+--------------+

来月誕生日を迎えるペットを見つけることも簡単です。今月は 4 月だとします。月の値は 4 であるため、5 月 (月 5) に生まれたペットは次のように探すことができます。

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

今月が 12 月の場合は多少複雑になります。月の番号 (12) に単に 1 を加算して 13 月に生まれたペットを探すということはできません。そのような月は存在しないからです。代わりに、1 月 (月 1) に生まれたペットを探します。

現在が何月であっても機能するクエリーを記述すると、特定の月の番号を使用する必要がなくなります。DATE_ADD() を使用すると、所定の日付に時間間隔を加算できます。CURDATE() の値に 1 か月を加算してから、月の部分を MONTH() で抽出すると、誕生日を調べる月が得られます。

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

現在の月の値が 12 の場合はモジュロ関数 (MOD) を適用して 0 に折り返してから、1 を加算する方法でも、同じタスクを達成できます。

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

MONTH()1 から 12 までの数値を返します。また、MOD(something,12)0 から 11 までの数値を返します。したがって、MOD() のあとで加算を行わないと、11 月から 1 月に進んでしまいます。


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.