Age calculation and Age function in SQL Server

AGE CALCULATION

1 Calculating Age as of End of the year

Sometime as business requirement dictate that we should calculate age at the time of end year ( this can be end of financial year, accounting year, measurement year, etc)

Let's say we have a table of employee with a field as Date_of_Birth or DOB.

DECLARE @AgeAsOf DATE = '2015-12-31

Select DOB, AGE = CASE WHEN dateadd(year, datediff (year, E.DOB, @AgeAsOf), E.DOB) > @AgeAsOf
THEN datediff (year, E.DOB, @AgeAsOf) - 1
ELSE datediff (year, E.DOB, @AgeAsOf)
END

FROM dbo.Employee


Say further that you have to select data based on certain age range and you have to passed this in your WHERE condition. Here we are looking for those employee who live in US
and are in age range between 35 and 50 only

DECLARE @AgeAsOf DATE = '2015-12-31

Select * FROM dbo.EMPLOYEE E
WHERE E.Employee_location = 'US'
AND (CASE WHEN dateadd(year, datediff (year, E.DOB, @AgeAsOf), E.DOB) > @AgeAsOf
THEN datediff (year, E.DOB, @AgeAsOf) - 1
ELSE datediff (year, E.DOB, @AgeAsOf)
END ) BETWEEN 35 AND 50

2. Calculating Age as of today (getdate())

You can use above query to get age as of today by replacing @AgeAsOf variable with getdate() function

Select DOB, AGE = AGE =CASE WHEN dateadd(year, datediff (year, E.DOB, getdate()), E.DOB) >getdate()
THEN datediff (year, E.DOB, getdate()) - 1
ELSE datediff (year, E.DOB, getdate())
END

FROM dbo.Employee


Select * FROM dbo.EMPLOYEE E
WHERE E.Employee_location = 'US'
AND (CASE WHEN dateadd(year, datediff (year, E.DOB, getdate()), E.DOB) > getdate()
THEN datediff (year, E.DOB, getdate() - 1
ELSE datediff (year, E.DOB, getdate())
END ) BETWEEN 35 AND 50


3. Creating a function to get AGE.
create function [dbo].[AgeAtGivenDate](
    @DOB    DATE,
    @PassedDate DATE
)

returns INT
with SCHEMABINDING
as
begin

DECLARE @iMonthDayDob INT
DECLARE @iMonthDayPassedDate INT


SELECT @iMonthDayDob = CAST(datepart (MM,@DOB) * 100 + datepart  (DD,@DOB) AS INT)
SELECT @iMonthDayPassedDate = CAST(datepart (MM,@PassedDate) * 100 + datepart  (DD,@PassedDate) AS INT)

RETURN DateDiff(YY,@DOB, @PassedDate)
- CASE WHEN @iMonthDayDob <= @iMonthDayPassedDate
  THEN 0
  ELSE 1
  END

END


--How to execute your function
DECLARE @ret INT;
EXEC @ret = [dbo].[AgeAtGivenDate] @DOB ='1923-08-28', @PassedDate = '2015-12-31'
PRINT @ret




Select  [dbo].[AgeAtGivenDate] ('1923-08-28',  '2015-12-31')

Select dbo.AgeAtGivenDate, DOB
FROM EMPLOYEE