SQLTeam.com | Weblogs | Forums

Find age in years, months and days


I want to calculate age in years, months. and days. Pls go through below formula and pls advice me , where i did mistake?

Select ename,floor(cast(DATEDIFF(d,hiredate, GETDATE())as varchar)/365.25) + ' Years ' +
floor(floor(cast(DATEDIFF(d,hiredate, GETDATE())as nvarchar)%365.25)/30.4375)+ ' Months ' +floor(floor(cast(DATEDIFF(d,hiredate, GETDATE())as nvarchar)%365.25)%30.4375)+ ' Days' as Age from empdetails

Thank you in advance


Your cast are off. Try this:

select ename
      ,cast(floor(datediff(day,hiredate,getdate())/365.25) as varchar)+' Years '
      +cast(floor(datediff(day,hiredate,getdate())%365.25/30.4375) as varchar)+' Months '
      +cast(floor(datediff(day,hiredate,getdate())%365.25%30.4375) as varchar)+' Days'
       as Age
  from empdetails