Oracle Age calculation from Date of birth and Today

Question

I want to calculate the current Age from Date of Birth in my Oracle function.

What I am using is (Today-Dob)/30/12, but this is not accurate as some months have 31 days.

I need to get the correct age with the maximum precision. How can I do that?

Inkhorn

take a look at this post, I think it has what you need: #44319 – Tiphanie

Just some pedantic comments. When we say "age" it's probably a good idea to think more carefully about what the word means. Normally, when someone says "age", they actually mean "how many birthday anniversaries have passed". Scientifically, if we express two peoples' ages in years, we cannot compare them perfectly, regardless of precision, because their actual age will be different depending on when they were born - e.g. were they born in a leap year; how many leap seconds have been added/subtracted since then; were they born prior to September 1752? (1/2) – Evita

(2/2) It depends on what the question actually is - i.e. "which of these two people lived the longest" should be answered using a unit of time which doesn't change signfiicantly (e.g. days, or seconds). "Has this person passed their 18th birthday" is a different question, which I'd say is closer to what most people mean by "age". – Evita

Finally: infiniteundo.com/post/25326999628/… – Evita

Answer 1

SQL> select trunc(months_between(sysdate,dob)/12) year,
  2         trunc(mod(months_between(sysdate,dob),12)) month,
  3         trunc(sysdate-add_months(dob,trunc(months_between(sysdate,dob)/12)*12+trunc(mod(months_between(sysdate,dob),12)))) day
  4  from (Select to_date('15122000','DDMMYYYY') dob from dual);

      YEAR      MONTH        DAY
---------- ---------- ----------
         9          5         26

SQL>

Spigot

Answer 2

For business logic I usually find a decimal number (in years) is useful:

select months_between(TRUNC(sysdate),
                      to_date('15-Dec-2000','DD-MON-YYYY')
                     )/12
as age from dual;

       AGE
----------
9.48924731

Evita

AskTOM (Connor McDonald) says it fails in very few cases: asktom.oracle.com/pls/apex/… Recommends this one: trunc((to_number(to_char(now,'YYYYMMDD'))- to_number(to_char(birth,'YYYYMMDD')))/10000) – Introrse

Hi @RaúlMoreno, that simple formula actually results in much lower precision. That article is about calculating an age in integer years only. Connor himself says "This is a reasonable approximation, but still...an approximation". My algorithm is much more accurate, and does not suffer from the original problem expressed in that link (dividing number of days by 365 will often be incorrect, for hopefully obvious reasons). – Evita

Oh! sorry, I didn't noticed he wanted decimals. I wanted the age, real, in years, having into account leap years, etc. and this one is the only one which seems to get it right always (and it's not the one which divides by 365). – Introrse

How do you convert the decimal-age back to a date(date of birth)? I have a question about this here: https://mcmap.net/q/427087/-convert-decimal-year-to-date/5576771 – Timoshenko

Answer 3

SELECT 
TRUNC((SYSDATE - TO_DATE(DOB, 'YYYY-MM-DD'))/ 365.25) AS AGE_TODAY FROM DUAL;

This is easy and straight to the point.

Monitorial

best answer but need to +1 day – Consentient

Answer 4

Age (full years) of the Person:

SELECT
  TRUNC(months_between(sysdate, per.DATE_OF_BIRTH) / 12) AS "Age"
FROM PD_PERSONS per

Quartersaw

Answer 5

Or how about this?

with some_birthdays as
( 
    select date '1968-06-09' d from dual union all
    select date '1970-06-10' from dual union all
    select date '1972-06-11' from dual union all
    select date '1974-12-11' from dual union all
    select date '1976-09-17' from dual
)
select trunc(sysdate) today
, d birth_date
, floor(months_between(trunc(sysdate),d)/12) age
from some_birthdays;

Decalescence

Answer 6

And an alternative without using any arithmetic and numbers (although there is nothing wrong with that):

SQL> with some_birthdays as
  2  ( select date '1968-06-09' d from dual union all
  3    select date '1970-06-10' from dual union all
  4    select date '1972-06-11' from dual union all
  5    select date '1974-12-11' from dual union all
  6    select date '1976-09-17' from dual
  7  )
  8  select trunc(sysdate) today
  9       , d birth_date
 10       , extract(year from numtoyminterval(months_between(trunc(sysdate),d),'month')) age
 11    from some_birthdays
 12  /

TODAY               BIRTH_DATE                 AGE
------------------- ------------------- ----------
10-06-2010 00:00:00 09-06-1968 00:00:00         42
10-06-2010 00:00:00 10-06-1970 00:00:00         40
10-06-2010 00:00:00 11-06-1972 00:00:00         37
10-06-2010 00:00:00 11-12-1974 00:00:00         35
10-06-2010 00:00:00 17-09-1976 00:00:00         33

5 rows selected.

Jaimiejain

Answer 7

You can try

SELECT ROUND((SYSDATE - TO_DATE('12-MAY-16'))/365.25, 5) AS AGE from DUAL;

You can configure ROUND to show as many decimal places as you wish.

Placing the date in decimal format like aforementioned helps with calculations of age groups, etc.

This is just a contrived example. In real world scenarios, you wouldn't be converting strings to date using TO_DATE.

However, if you have date of birth in date format, you can subtract two dates safely.

Sliver

Given that it's an Age, might also be useful to consider using FLOOR instead of ROUND - If you're 29.99 years old, you'd still call yourself 29! – Chian

Answer 8

This seems considerably easier than what anyone else has suggested

select sysdate-to_date('30-jul-1977') from dual; 

Thanksgiving

This is incorrect as it's implicitly converting a string to a date, which may well go horribly wrong. – Prussia

You should always include the date format in the call to TO_DATE(...) so you get correct/consistent results regardless of user/session preferences re: locale, etc. – Aceae

Answer 9

Suppose that you want to have the age (number of years only, a fixed number) of someone born on June 4, 1996, execute this command :

SELECT TRUNC(TO_NUMBER(SYSDATE - TO_DATE('04-06-1996')) / 365.25) AS AGE FROM DUAL;

Result : (Executed May 28, 2019)

       AGE
----------
        22

Explanation :

• SYSDATE : Get system's (OS) actual date.
• TO_DATE('04-06-1996') : Convert VARCHAR (string) birthdate into DATE (SQL type).
• TO_NUMBER(...) : Convert a date to NUMBER (SQL type)
• Devide per 365.25 : To have a bissextile year every four years (4 * 0.25 = 1 more day).
• Trunc(...) : Retrieve the entire part only from a number.

Beowulf

Answer 10

SQL>select to_char(to_date('19-11-2017','dd-mm-yyyy'),'yyyy') -  to_char(to_date('10-07-1986','dd-mm-yyyy'),'yyyy') year,
to_char(to_date('19-11-2017','dd-mm-yyyy'),'mm') -  to_char(to_date('10-07-1986','dd-mm-yyyy'),'mm') month,
to_char(to_date('19-11-2017','dd-mm-yyyy'),'dd') -  to_char(to_date('10-07-1986','dd-mm-yyyy'),'dd') day from dual;

      YEAR      MONTH        DAY
---------- ---------- ----------
        31          4          9

Airdrome

Answer 11

    SELECT FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE, 'DD'), dob)/12)
    FROM dual;

Christianize

Welcome to Stack Overflow. Please read How to Answer. This is very similar to many answers that are already here. Specifically, why is this a better solution? – Vibrant

Answer 12

Use the below.

round(months_between(TRUNC(sysdate),date_of_birth)/12) as age,

Galvanism

Answer 13

You can try below method,

SELECT EXTRACT(YEAR FROM APP_SUBMITTED_DATE)-EXTRACT(YEAR FROM BIRTH_DATE) FROM SOME_TABLE;

It will compare years and give age accordingly.
You can also use SYSDATE instead of APP_SUBMITTED_DATE.

Regards.

Ethical

Answer 14

Select trunc(to_number(sysdate - to_date(dob) /365.25)) as Age from desingh

AGE

26 25 31

Suprematism

Answer 15

select (extract(year from current_date)-extract(year from Date_of_birth)) as Age from table_name;`

age=current_year - birth_year;

extract(year/month/date from date) //oracle function for extracting values from date

Obliquely

Answer 16

select (SYSDATE-DOB)/365 "Age" from dual

Million