Click here to Skip to main content
15,559,455 members
Please Sign up or sign in to vote.
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
Comments
[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

calculate using
select DATEDIFF(yy, '1980-12-31', getdate())
 
Share this answer
 
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]
 
Share this answer
 
v3
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[^]
 
Share this answer
 
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]
 
Share this answer
 
v5

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



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