Dedicated to design and performance of databases and audio systems.
Today I was asked what was the best way was to calculate age--in whole years.
I have seen many different attempts at it over time. Everything from
SUBSTRING
-ing the date components apart and figuring the difference in years, if one month value is greater than or equal to the other, and the same for the days, andIn short, save yourself a lot of hassle. There is a very simple way to do it in Teradata. You knew there would be :-)
Example, how many years have elapsed between September 21, 2000 and today, September 20, 2013. The answer is twelve since thirteen years have not yet fully elapsed.
If we performed something like
select (date '2013-09-20' - date '2000-09-21') / 365;
or
select (date '2013-09-20' - date '2000-09-21') / 365.25;
The answers would be 13 and 13.00, respectively. But we want it to be 12.
Instead, we are going to CAST
our DATE
values as INTEGER
. This way 9/20/2013 will now be 1130920 and 9/21/2000 now 1000921. Then if we take the difference between the two values, it is 129999. Divide this value by 1000 and the result is 12. Twelve years, not thirteen.
select
(cast(date '2013-09-20' as integer) -
cast(date '2000-09-21' as integer)) / 10000
; -- results in 12
It is important to remember a few things:
DATE
s are internally stored as INTEGER
s; therefore,
Integer math in Teradata truncates to the whole INTEGER
value. So, 129,999 / 10,000 = 12, not 12.9999.
Using dates prior to 1900 would be problematic and the algorithm would have to be adjusted accordingly to compensate.
I hope this helps with your future age calculation needs.
Cheers
-Brad