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

Rate

5 (1)

Share

Share

Rate

5 (1)