How to find age on a particular date

This function can be used to calculate the Age on a particular date.

CREATE FUNCTION dbo.FindAge

(

@Dob    DATETIME,

@ToDate DATETIME

)

RETURNS INT

AS

    BEGIN

        RETURN ROUND((CASE

                          WHEN @dob > @ToDate

                          THEN NULL

                          WHEN DATEPART(day, @dob) > DATEPART(day, @ToDate)

                          THEN DATEDIFF(month, @dob, @ToDate) - 1

                          ELSE DATEDIFF(month, @dob, @ToDate)

                      END / 12), 0);

    END;

Please refer why you should not use DATEDIFF function directly and use above logic.

http://www.sqlteam.com/article/datediff-function-demystified

Cheers!

Uma