SQLTeam.com | Weblogs | Forums

Find age in years, months and days


#1

Hi,

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

Regards
ajaypal


#2

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
;