Function to calculate age

Question

I am trying to write a function where I can find age based on the date the record was inserted rather than getdate(). I want to filter the user who are less than 18 years when they registered. If I query it after a year, it should still show the user as 17 based on record insert date than current date. This is what I wrote but it is still giving the age based on current date than the record insert date. Any suggestions would be really helpful. Thank You

--InputDate as DateOfBirth
--InsertDate as date the record was inserted

 CREATE FUNCTION [dbo].[FindAge] (@InputDate int, @Insertdate datetime )
 RETURNS int
 AS
 BEGIN
 DECLARE @Age as Int

 DECLARE @d DATETIME
SET @d = CONVERT(DATETIME, CONVERT(VARCHAR(8), @InputDate), 112)

SELECT @Age=DATEDIFF(year, @d, @Insertdate)
- CASE WHEN DATEADD(year, DATEDIFF(year, @d, @Insertdate), @d) <=  GetDate()
       THEN 0 ELSE 1 END      

RETURN @Age
END

---- Drop Obselete procs 
GO

Update Followed Bacon Bits suggestion and it worked out perfectly.

Answer 1

All DATEDIFF() does is subtract the years from the date components. It's very stupid:

select datediff(yy,'2000-12-19','2014-01-01') --14
select datediff(yy,'2000-12-19','2014-12-18') --14
select datediff(yy,'2000-12-19','2014-12-19') --14
select datediff(yy,'2000-12-19','2014-12-20') --14
select datediff(yy,'2000-12-19','2014-12-31') --14
select datediff(yy,'2000-12-19','2015-01-01') --15
select datediff(yy,'2000-12-19','2015-12-31') --15
select datediff(yy,'2000-12-19','2016-01-01') --16
select datediff(yy,'2000-12-19','2016-12-31') --16

Don't calculate the number of hours in a year with the year being 365.25 days long or something like that. It's an exercise in futility, and just guarantees that you will be wrong near every person's birthday.

Your best bet is to calculate it how humans do it. In the US (and most Western nations, I believe) it's the difference between the years, but you only count the current year when you pass your birthday:

declare @birthdate date = '2000-12-19';

declare @target date;

SELECT DATEDIFF(yy, @birthdate, @target)
       - CASE 
           WHEN (MONTH(@birthdate) > MONTH(@target))
               OR (
                   MONTH(@birthdate) = MONTH(@target)
                   AND DAY(@birthdate) > DAY(@target)
               )
           THEN 1
           ELSE 0
        END

Here's the values you'd get:

set @target = '2014-01-01' --13
set @target = '2014-12-18' --13
set @target = '2014-12-19' --14
set @target = '2014-12-20' --14
set @target = '2014-12-31' --14
set @target = '2015-01-01' --14
set @target = '2015-12-31' --15
set @target = '2016-01-01' --15
set @target = '2016-12-31' --16

Change @target to getdate() to calculate the age as of now.

If your region uses East Asian age reckoning, however, you'll need to use a completely different method to determine what age a person is since they're considered age 1 on the day they're born, and their age increases each February.