Function to Return Age of a Person
This Function is a small script to retrieve the age of person based on date of birth to a particular date.
Eg.
select dbo.GetDateofbirth ('01/01/1975',GETDATE())
RETURNS
29Years 2Months 24Days that is the age.
TESTED only on SQLSERVER 2000
CREATE FUNCTION GetDateofbirth (@DOB datetime,@currentDate datetime )
Returns VARCHAR(100)
AS
BEGIN
DECLARE @out_date VARCHAR(100),
@l_dtCurrentDate DATETIME,
@l_dtPrevDate DATETIME,
@l_dtDate DATETIME,
@l_dtEntered DATETIME,
@years INT,
@months INT,
@days INT
SET @l_dtPrevDate = @DOB
SET @l_dtCurrentDate = @currentDate
SET @years = DATEDIFF("yyyy", @l_dtPrevDate, @l_dtCurrentDate)
IF CAST(CONVERT(VARCHAR(2),MONTH(@l_dtPrevDate)) + '/' + CONVERT(VARCHAR(2),DAY(@l_dtPrevDate)) + '/' + CONVERT(VARCHAR(4),YEAR(@l_dtCurrentDate)) AS DATETIME ) > @l_dtCurrentDate
BEGIN
SET @years = @years - 1
END
SET @l_dtDate = DATEADD("yyyy", @years, @l_dtPrevDate)
SET @months = DATEDIFF("m", @l_dtDate, @l_dtCurrentDate)
SET @l_dtEntered = DATEADD("m", @months, @l_dtDate)
IF CAST(CONVERT(VARCHAR(2),MONTH(@l_dtEntered)) + '/' + CONVERT(VARCHAR(2),DAY(@l_dtEntered)) + '/' + CONVERT(VARCHAR(4),YEAR(@l_dtCurrentDate)) AS DATETIME ) > @l_dtCurrentDate
BEGIN
SET @months = @months - 1
SET @l_dtEntered = DATEADD("m", -1, @l_dtEntered)
END
SET @days = DATEDIFF("d", @l_dtEntered, @l_dtCurrentDate)
SET @out_date = CONVERT(VARCHAR(5),@years) + 'Years ' + CONVERT(VARCHAR(5),@months) + 'Months ' +CONVERT(VARCHAR(5),@days) + 'Days '
RETURN @out_date
END 
