February 26, 2018 at 6:29 pm
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 = 7
How about Birthday is 20100515, current date is 20180515?
AGE = 20180515 - 20100515 = 80000. 80000 / 10000 = 8
Maybe DOB is 20171231 and current date is 20180101?
AGE = 20180101 - 20171231= 8870. 8870/ 10000 = 0
I'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;
February 26, 2018 at 9:16 pm
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-sql
When 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 parameters
DECLARE @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?
February 26, 2018 at 9:40 pm
george-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 parameters
DECLARE @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 valueDECLARE @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;
February 26, 2018 at 9:48 pm
george-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 parameters
DECLARE @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
February 27, 2018 at 9:16 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 27, 2018 at 10:12 am
ScottPletcher - 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.
February 27, 2018 at 11:34 am
curt 27476 - Tuesday, February 27, 2018 10:12 AMScottPletcher - 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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply