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.

Here you go: http://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate — CodeLikeBeaker, Dec 19 '14 at 21:20
After a quick glance, this looks like it *should* work. Perhaps the `@InputDate` parameter has the wrong value in it? — Andrew, Dec 19 '14 at 21:22
My code already gets the age based on getdate but I need the age based on createdate and I thought the code I posted should handle it but still it is calculating age based on getdate() than createdate. — user2908229, Dec 19 '14 at 21:22
I inserted few test records yesterday with birthday set as today to test the function. So, my sample had 17,25,35,45 and if this function worked as it should the the age should still return the same value but it is returning 18,26,36 and 46 which means that this function is finding age based on getdate. — user2908229, Dec 19 '14 at 21:26

Bacon Bits · Accepted Answer · 2014-12-20T01:59:03.643

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.

Function to calculate age

1 Answers1