15,616,232 members
3.00/5 (1 vote)
See more:
How to calculate age in Year,Month and Days from date of birth in MySql.

i used an existing query to get the result,like
SQL
```select TIMESTAMPDIFF( YEAR, dojDate,now()) as years
, TIMESTAMPDIFF( MONTH, dojDate,now()) % 12 as months
, FLOOR( TIMESTAMPDIFF( DAY, dojDate,now()) % 30.4375 ) as days```

but the issue is that,
Suppose
```DOJ : '2010-01-01'
NOW : '2011-01-02'
O/P : 1 Year ,0 Month, 0 Days
Correct.

DOJ : '2010-01-01'
NOW : '2011-01-01'
O/P : 1 Year ,0 Month, 30 Days
Actually this should return 0 Year, 11 Month, 30 Days```

How to get this??
Posted
[no name] 28-Jan-15 5:11am
you can also use
select convert(int,DATEDIFF(d, '1990-12-19', getdate())/365.25)
[no name] 28-Jan-15 5:29am
It work For me

Solution 4

Got the Solution!!!

SQL
```Select TIMESTAMPDIFF(YEAR, date2, date1) as Years,

TIMESTAMPDIFF
(
MONTH,
date2,
(
SUBDATE(date1,INTERVAL TIMESTAMPDIFF(YEAR, date2, date1) YEAR)
)
)
as Months,

TIMESTAMPDIFF
(
DAY,
date2,
(
SUBDATE(SUBDATE(date1,INTERVAL TIMESTAMPDIFF(YEAR, date2, date1) YEAR), INTERVAL TIMESTAMPDIFF
(
MONTH,
date2,
(
SUBDATE(date1,INTERVAL TIMESTAMPDIFF(YEAR, date2, date1) YEAR)
)
) MONTH)
)
)
as Days;```

[reference]

v5

Solution 3

How to calculate Age using C#[^]

Another one from StackTrace
http://stackoverflow.com/questions/9/how-do-i-calculate-someones-age-in-c[^]

Solution 2

use this query :

SQL
```select extract(year from from_days(days)) - 1600 as years
, extract(month from from_days(days)) - 1 as months
, extract(day from from_days(days)) - 1 as days
from ( select to_days(now()) - to_days(a.dojDate) +
to_days(str_to_date('1600-01-01', '%Y-%m-%d')) as days
from sampletable a ) as b```

http://sqlfiddle.com/#!2/8d1a6/12[reference]

v3

Solution 1

calculate using
select DATEDIFF(yy, '1980-12-31', getdate())