## Calculating Age - Keep it simple

 Author Message curtw SSC-Enthusiastic Group: General Forum Members Points: 123 Visits: 19 I've read umpteen articles and debates about how to calculate age in TSQL. Seems there should be an AGE function, right?I've used the same function for years but I've never seen a similar solution proposed in an article. In my opinion, the simplest way to calculate age - and the easiest to understand - is a single subtraction.Birthday is 20100515. Assume current date is 20180514. AGE = 20180514 - 20100515 = 79999.Because this is an integer, simply divide by 10000: 79999 / 10000 = 7How about Birthday is 20100515, current date is 20180515?AGE = 20180515 - 20100515 = 80000. 80000 / 10000 = 8Maybe DOB is 20171231 and current date is 20180101?AGE = 20180101 - 20171231= 8870. 8870/ 10000 = 0I'll share my function, in case you like this approach. Works ever time - unless you're older than 2,147,483,647, in which case you may need to code a try catch.`CREATE FUNCTION dbo.AGE (@BornOn DATE, @AgeOn DATE) RETURNS int AS BEGIN DECLARE @AgeOut AS INT = (CAST(CONVERT(VARCHAR(8), @AgeOn, 112) AS INT) - CAST(CONVERT(VARCHAR(8), @BornOn, 112) AS INT)) / 10000; RETURN(@AgeOut); END;`Some ways to call the function:`SELECT AgeInYears = dbo.Age('20100515', '20180514');SELECT AgeInYears = dbo.Age('5/15/2010', '5/15/2018');DECLARE @DOB DATE = '12/31/2017';DECLARE @Today DATE = '1/1/2018';SELECT AgeInYears = dbo.Age(@DOB, @Today);`To skip the function and calculate age inline, try this:`DECLARE @DOB DATE = '12/31/2017';DECLARE @Today DATE = '1/1/2018';SELECT Age = (CAST(CONVERT(VARCHAR(8), @Today, 112) AS INT) - CAST(CONVERT(VARCHAR(8), @DOB, 112) AS INT)) / 10000;` george_at_sql Ten Centuries Group: General Forum Members Points: 1270 Visits: 1745 Hmm, there is already a datediff function which will tell you the difference between two dates. And it can return the values in whichever format you like the difference --> in days, months, years etc.. In addition there is no overhead of converting the date datatype to that of an integer.https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sqlWhen we comparing two columns , it is best to avoid this overhead of conversion.The function you have created how would it work when you pass the following parametersDECLARE @DOB DATE = '19991231';DECLARE @Today DATE = '20010101';It tells you there are 18870 integers between 31-Dec-1999 and 1-Jan-2001 and divide by 10000=0.18 which means age is less than a year? DesNorton SSCoach Group: General Forum Members Points: 18044 Visits: 7590 +xgeorge-178499 - Monday, February 26, 2018 9:16 PMHmm, there is already a datediff function which will tell you the difference between two dates. And it can return the values in whichever format you like the difference --> in days, months, years etc.. In addition there is no overhead of converting the date datatype to that of an integer.https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sqlWhen we comparing two columns , it is best to avoid this overhead of conversion.The function you have created how would it work when you pass the following parametersDECLARE @DOB DATE = '19991231';DECLARE @Today DATE = '20010101';It tells you there are 18870 integers between 31-Dec-1999 and 1-Jan-2001 and divide by 10000=0.18 which means age is less than a year?Be careful of using DATEDIFF for age calcs. It counts the number of period boundaries that have passed, so you are often off by 1.in the following examples, using DateDiff returns the incorrect value`DECLARE @DOB DATE = '20160105';DECLARE @Today DATE = '20180104';SELECT Age_Calc = (CAST(CONVERT(VARCHAR(8), @Today, 112) AS INT) - CAST(CONVERT(VARCHAR(8), @DOB, 112) AS INT)) / 10000;SELECT Age_DateDiff_Year = DATEDIFF(YEAR, @DOB, @Today);SELECT Age_DateDiff_Month = DATEDIFF(MONTH, @DOB, @Today) /12;SELECT Age_DateDiff_Day = DATEDIFF(DAY, @DOB, @Today) /365;` How to post data/code on a forum to get the best help.Make sure that you include code in the appropriate IFCode tags. curtw SSC-Enthusiastic Group: General Forum Members Points: 123 Visits: 19 +xgeorge-178499 - Monday, February 26, 2018 9:16 PMHmm, there is already a datediff function which will tell you the difference between two dates. And it can return the values in whichever format you like the difference --> in days, months, years etc.. In addition there is no overhead of converting the date datatype to that of an integer.https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sqlWhen we comparing two columns , it is best to avoid this overhead of conversion.The function you have created how would it work when you pass the following parametersDECLARE @DOB DATE = '19991231';DECLARE @Today DATE = '20010101';It tells you there are 18870 integers between 31-Dec-1999 and 1-Jan-2001 and divide by 10000=0.18 which means age is less than a year?Hi George!You got the 18870 right. 18870 / 10000 = 1. (1.887 = 1 with integer division.) Your subject is 1 year old.Overhead: Sure, date to varchar, varchar to integer. There is overhead. If you are running this for 500MM rows, in a single query, you might want write code that is un-readable but performs. But if you are running this one calc at a time - or even 1MM rows at a time - the overhead is minimal and the reliability + readability is king (in my opinion). Benchmark this (I have), you may be surprised....Thanks!Curt ScottPletcher SSC Guru Group: General Forum Members Points: 82734 Visits: 9331 As usual with age, leap year birthdays are the deciding factor.If someone was born on Feb 29, 2004, how old are they on Feb 28, 2018? In your code, 13. But in some jurisdictions, legally that person is 14 on Feb 28. In other jurisdictions, you can use Mar-1 as their birthday in non-leap years.Edit: Btw, get rid of the variable in the function, it adds slight overhead unnecessarily:`CREATE FUNCTION dbo.Age (@BornOn DATE, @AgeOn DATE) RETURNS int AS BEGIN RETURN ( SELECT (CAST(CONVERT(VARCHAR(8), @AgeOn, 112) AS int) - CONVERT(VARCHAR(8), @BornOn, 112)) / 10000)END;` SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them. curtw SSC-Enthusiastic Group: General Forum Members Points: 123 Visits: 19 +xScottPletcher - Tuesday, February 27, 2018 9:16 AMAs usual with age, leap year birthdays are the deciding factor.If someone was born on Feb 29, 2004, how old are they on Feb 28, 2018? In your code, 13. But in some jurisdictions, legally that person is 14 on Feb 28. In other jurisdictions, you can use Mar-1 as their birthday in non-leap years.Edit: Btw, get rid of the variable in the function, it adds slight overhead unnecessarily:Hi Scott,Thanks for your comments!My function gets the correct age, but I agree, there will always be one-off business rules to be applied. In your leap-year example, the age would not be augmented, instead, the rule would be applied outside the function. For example, a jurisdiction such as you mentioned would not alter a subjects age, but they might allow the subject to buy beer at 20 if non-leap year, and date is Feb 28, and subject DOB is Feb 29. Heck, I could pass a law that grants you the grandpa discount if your dog has pups, but that wouldn't actually make you a grandpa - and your subject is still 20 on Feb 28.I agree, the variable is not needed. More of a style issue where I normally use vars to standardize return values across functions. You might be surprised, but benchmarks won't improve measurably by removing this var. Note, this is not interpreted code. ScottPletcher SSC Guru Group: General Forum Members Points: 82734 Visits: 9331 +xcurt 27476 - Tuesday, February 27, 2018 10:12 AM+xScottPletcher - Tuesday, February 27, 2018 9:16 AMAs usual with age, leap year birthdays are the deciding factor.If someone was born on Feb 29, 2004, how old are they on Feb 28, 2018? In your code, 13. But in some jurisdictions, legally that person is 14 on Feb 28. In other jurisdictions, you can use Mar-1 as their birthday in non-leap years.Edit: Btw, get rid of the variable in the function, it adds slight overhead unnecessarily:Hi Scott,Thanks for your comments!My function gets the correct age, but I agree, there will always be one-off business rules to be applied. In your leap-year example, the age would not be augmented, instead, the rule would be applied outside the function. For example, a jurisdiction such as you mentioned would not alter a subjects age, but they might allow the subject to buy beer at 20 if non-leap year, and date is Feb 28, and subject DOB is Feb 29. Heck, I could pass a law that grants you the grandpa discount if your dog has pups, but that wouldn't actually make you a grandpa - and your subject is still 20 on Feb 28.I agree, the variable is not needed. More of a style issue where I normally use vars to standardize return values across functions. You might be surprised, but benchmarks won't improve measurably by removing this var. Note, this is not interpreted code.Yeah, it's interesting how complicated this "simple" age thing can be!But, no, the age is 21, you cannot buy beer at 20, that is illegal. Nor get your pension at an earlier age (there could be a significant dollar difference here). You must give the person the correct age, not just pretend they are a different age for one day for whatever reason.As to unnecessary local variables, I'm not going to waste resources on it, particularly since it's the only local variable used. I've found extraordinarily few scalar functions that actually need a local variable to store the return value. SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.