calculate age in stored procedure

[Jassim Rahma]

I want to know how can I calculate the person's age from a person_birthday datetime field to show the year and month(if any) so if the age is exactly 25 then it should be 25y or if it's less than a year it will be 4m otherwise 25y 2m?

[Mycroft Holmes]

Use DateDiff(yyyy,birhtday,getdate()) to get the difference, the rest is just an excercise in maths and logic. DATEDIFF is the tool you need.

Never underestimate the power of human stupidity RAH

[Pranay Rana]

try this select cast(DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 as varchar(10))+ ' Y ' + cast (((DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate())) - (DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12) ) as varchar(10)) + 'M' and for your scenario you also have to use case when

[Pranay Rana]

FINAL SOLUTION select case WHEN (DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 ) = 300 THEN (cast(DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 as varchar(10))+ ' Y ') WHEN (DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 ) > 300 THEN (cast(DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 as varchar(10))+ ' Y ' + cast (((DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate())) - ((DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12))*12 ) as varchar(10)) + 'M') WHEN (DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 ) < 300 THEN (cast(DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 as varchar(10))+ ' M ') END