Using Teradata SQL: Finding a person's age
How to calculate a person's age correctly given a date and their date of birth in Teradata SQL.
Sometimes you need to find out a person's age[1]. You can't simply subtract the Date of Birth (DOB) from the current date as that just gives you the number of days between the two dates. Yes, you can divide that by 365 but that won't take into account leap years. Dividing by 365.25 does take into account leap years but not fully, like is there actually a leap year between the two dates you're checking, or is one of them a century, etc[2]. Fortunately the calculation below does take all of this into account and gives the correct age under all circumstances. I did a lot of testing on this 😊.
(CAST(CURRENT_DATE AS INTEGER) - CAST(DOB AS INTEGER))/10000 AS Age
It works because when Teradata converts dates to an Integer, it stores it as (year-1900)mmdd, so 21st June 2019 would be 1190621. Therefore, if you subtract one date from another it gives you the difference between them as number of whole years, plus a component based on the months and days. You divide by 10,000 to left shift the decimal place by 4 places, so you cut out the mmdd component of the date, that means you’re just left with the yy part as an integer. This is the same as your age. Neat 😀
Note: This only works on years, it doesn't work on months or days without modification.
“I am not young enough to know everything.” ― Oscar Wilde
Ages are always expressed as an integer value, so if you are 34 years, 11 months and 30 days old, your age is 34. ↩︎
The exact rules for deciding if a year is a leap year are: If the year is divisible by 4 with no remainder, it is a leap year; except if it is a complete century like 1900, then it is not a leap year; except if it is a millennium like 2000, in which case it is a leap year. ↩︎