Technical Article

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)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating