Function for calculating age

Question

I am trying to write a function where I can find the age based on the date of recording, not getdate (). I want to filter a user who is under 18 years old when they register. If I request it after a year, it should show the user as 17 based on the date the record was inserted than the current date. This is what I wrote, but it still gives an age based on the current date than the date the entry was entered. 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 The next Bacon Bits offer and it worked out perfectly.

Answer 1

All DATEDIFF() is to subtract years from date components. It is so 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 

Do not count the number of hours in a year with a year of 365.25 days or something like that. This exercise is useless and simply ensures that you will be wrong next to every birthday.

It’s best to figure out how people do it. In the USA (and in most western countries, I think) this is the difference between the years, but you only count the current year when you celebrate 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 are the values ​​you received:

 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 current age.

If your region uses East Asian age , however, you will need to use a completely different method to determine how old a person is because they are considered age 1 on their birthday, and their age increases every February.