SQL: Calculate age accurately

–select datediff(year,’11/30/2005′,’01/31/2006′) as AGE;

–11/30/2005
–01/31/2006

–use AdventureWorks2014;
–go
CREATE function [dbo].[fnCalculateAge](@dob DateTime)
RETURNS nvarchar(50) AS

BEGIN

declare @tempdate DateTime, @years INT, @months INT, @days INT;
select @tempdate=@dob;

select @years= DATEDIFF(year,@tempdate,GETDATE())-
CASE
WHEN (month(@dob)>month(GETDATE()))
OR
(month(@dob)=month(GETDATE()) AND day(@dob)>day(GETDATE()))
THEN 1
ELSE 0
END

select @tempdate=DATEADD(year, @years, @tempdate);

select @months=DATEDIFF(month, @tempdate, GETDATE()) –
CASE
WHEN (day(@dob)> day(GETDATE()))
THEN 1 ELSE 0
END

select @tempdate=DATEADD(month,@months,@tempdate)

select @days=DATEDIFF(day,@tempdate,GETDATE())

DECLARE @AGE nvarchar(50)
SET @AGE=CAST(@years as nvarchar(4)) +’ Years ‘ + cast(@months as nvarchar(2)) + ‘ Months ‘ + cast(@days as nvarchar(4)) + ‘ Days’

RETURN @AGE
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s