15,607,633 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 1

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

## 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 3

Please refer this [solved] code project link
How to calculate Age using C#[^]

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

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 85 Chris Copeland 75 merano99 40 Richard MacCutchan 38 Maciej Los 30
 OriginalGriff 2,216 Richard MacCutchan 983 Graeme_Grant 640 Dave Kreskowiak 621 CHill60 275

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900