29

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?

4
  • take a look at this post, I think it has what you need: stackoverflow.com/questions/43819/…
    – Jay
    Jun 10 '10 at 14:46
  • 2
    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) May 25 '17 at 0:57
  • 1
    (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". May 25 '17 at 0:57
  • Finally: infiniteundo.com/post/25326999628/… May 25 '17 at 0:58

15 Answers 15

56
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>
0
22

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
4
  • 2
    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) May 10 '17 at 13:49
  • 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). May 11 '17 at 1:17
  • 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). May 24 '17 at 19:44
  • How do you convert the decimal-age back to a date(date of birth)? I have a question about this here: stackoverflow.com/q/47504583/5576771
    – Wilson
    Nov 27 '17 at 16:14
13
SELECT 
TRUNC((SYSDATE - TO_DATE(DOB, 'YYYY-MM-DD'))/ 365.25) AS AGE_TODAY FROM DUAL;

This is easy and straight to the point.

1
  • best answer but need to +1 day
    – CSK
    Oct 16 '18 at 15:41
8

Age (full years) of the Person:

SELECT
  TRUNC(months_between(sysdate, per.DATE_OF_BIRTH) / 12) AS "Age"
FROM PD_PERSONS per
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;
3

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.
3

This seems considerably easier than what anyone else has suggested

select sysdate-to_date('30-jul-1977') from dual; 
1
  • 2
    This is incorrect as it's implicitly converting a string to a date, which may well go horribly wrong.
    – Ben
    Jan 21 '15 at 8:51
3

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.

1
  • 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! Jul 1 '21 at 11:31
1

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.
0
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
0

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.

0

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

AGE

26 25 31

0
    SELECT FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE, 'DD'), dob)/12)
    FROM dual;
1
  • 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?
    – Chris
    May 13 '21 at 17:49
-1
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

-4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.