SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculating Age - Keep it simple


Calculating Age - Keep it simple

Author
Message
curtw
curtw
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 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 = 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;

george_at_sql
george_at_sql
SSC Eights!
SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)

Group: General Forum Members
Points: 803 Visits: 1305
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?
DesNorton
DesNorton
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16261 Visits: 6810
george-178499 - Monday, February 26, 2018 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?

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
curtw
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 19
george-178499 - Monday, February 26, 2018 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?

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
ScottPletcher
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75965 Visits: 8900
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
curtw
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 19
ScottPletcher - Tuesday, February 27, 2018 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:


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
ScottPletcher
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75965 Visits: 8900
curt 27476 - Tuesday, February 27, 2018 10:12 AM
ScottPletcher - Tuesday, February 27, 2018 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:


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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search