b¬äd†ødd

Calculating Age

September 20, 2013 by Brad

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

• determining the the number of days between some date and a birth date and dividing it by 365 or 365.25
• to 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, and
• a few others.

In 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:

• DATEs are internally stored as INTEGERs; therefore,
• subtract 1900 from the year,
• multiply it by 10,000,
• multiply the month by 100,
• add the year and month results, and
• then add the day value.

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