How to get age from date of birth?

Question

Oracle

In Oracle DATEDIFF does not work, and then how to display age by date of birth, given that there was no birthday this year. And is it possible to do without SYSDATE, i.e. not to get the current date from the system?

Answer 1

In fact, DATEDIFF is of no use to you at all. You just need to subtract the year of birth from the current one, and then decrease the result by one if the month of birth is greater than the current one or the months are equal, but the date is greater.
You can reduce this to one formula year * 10000 + month * 100 + day.
Divide the difference by 10,000 to get the age.

Answer 2

We also need the date of death.
For MS SQL.

CAST( DATEDIFF( Year, BirthDay, ISNULL( DeathDay, GETDATE())) 
 + ( SIGN ( DATEDIFF ( DAY, BirthDay, DATEADD ( YEAR, YEAR( BirthDay ) 
 - YEAR( ISNULL( DeathDay, GETDATE()) ), 
 ISNULL( DeathDay, GETDATE())))) - 1 ) / 2 AS VARCHAR)

This is a "magic" formula that takes into account the leap year.

Answer 3

In mssql, I brought the date dr to the current year, and there it’s already simple if it was dr (the given date is less than the current one) it means so many years, if it didn’t exist, then it’s one less.