Saturday 19 October 2013

Calculate Age in SQL Server

-- select dbo.FN_CalculateAge('1/21/1990') doB   -- DD/MM/YYYY.
Create FUNCTION [dbo].[FN_CalculateAge](@dayOfBirth datetime)
RETURNS Varchar(300)
AS 
BEGIN 
 
 DECLARE @Age varchar(300)
 DECLARE @today datetime, @thisYearBirthDay datetime
 DECLARE @years int, @months int, @days int 
 SELECT @today = GETDATE() 

 SELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth) 
 SELECT @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END) 
 SELECT @months = MONTH(@today - @thisYearBirthDay) - 1 
 SELECT @days = DAY(@today - @thisYearBirthDay) - 1 
 Set @Age=Cast(@years  As Varchar)+' years '+ Cast(@months  As Varchar)+' months '+Cast(@days As Varchar) +' days'
RETURN @Age 
END

No comments:

Post a Comment