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?

take a look at this post, I think it has what you need: http://stackoverflow.com/questions/43819/oracle-equivalent-to-sql-server-sybase-datediff — Jay, Jun 10 '10 at 14:46
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) — Jeffrey Kemp, May 25 '17 at 00:57
(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". — Jeffrey Kemp, May 25 '17 at 00:57
Finally: http://infiniteundo.com/post/25326999628/falsehoods-programmers-believe-about-time — Jeffrey Kemp, May 25 '17 at 00:58

score 60 · Accepted Answer · answered Jun 10 '10 at 14:51

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>

score 25 · Answer 2 · answered Jun 11 '10 at 02:07

25

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

answered Jun 11 '10 at 02:07

Jeffrey Kemp

59,135
14
106
158

3

AskTOM (Connor McDonald) says it fails in very few cases: https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9531934000346471628 Recommends this one: trunc((to_number(to_char(now,'YYYYMMDD'))- to_number(to_char(birth,'YYYYMMDD')))/10000) – Raúl Moreno 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). – Jeffrey Kemp May 11 '17 at 01: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). – Raúl Moreno 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: https://stackoverflow.com/q/47504583/5576771 – User1974 Nov 27 '17 at 16:14

score 14 · Answer 3 · answered Mar 09 '17 at 17:50

14

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

This is easy and straight to the point.

answered Mar 09 '17 at 17:50

HushMamba

270
2
7

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

score 13 · Answer 4 · answered Aug 29 '17 at 10:40

13

Age (full years) of the Person:

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

answered Aug 29 '17 at 10:40

provisota

1,390
1
14
14

score 5 · Answer 5 · edited Jan 11 '13 at 12:49

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;

score 3 · Answer 6 · edited Aug 24 '12 at 07:33

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.

score 3 · Answer 7 · answered May 16 '19 at 13:22

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.

answered May 16 '19 at 13:22

Ozil

945
3
11
28

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! – Barry Piccinni Jul 01 '21 at 11:31

score 2 · Answer 8 · answered May 21 '13 at 19:13

2

This seems considerably easier than what anyone else has suggested

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

answered May 21 '13 at 19:13

user2406874

29
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 08:51
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. – Eric Kramer Nov 29 '22 at 20:09

score 2 · Answer 9 · edited Jun 20 '20 at 09:12

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.

score 1 · Answer 10 · edited Nov 20 '17 at 08:09

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

score 1 · Answer 11 · answered May 13 '21 at 05:34

1

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

answered May 13 '21 at 05:34

Sergey

11
1

Welcome to Stack Overflow. Please read [answer]. This is very similar to many answers that are already here. Specifically, why is this a better solution? – ChrisGPT was on strike May 13 '21 at 17:49

score 0 · Answer 12 · answered Aug 19 '19 at 11:02

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.

answered Aug 19 '19 at 11:02

Hardik Vaghani

2,163
24
46

score 0 · Answer 13 · answered Apr 04 '21 at 04:56

0

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

AGE

26 25 31

answered Apr 04 '21 at 04:56

Desingh

1
1

score -1 · Answer 14 · edited Apr 07 '20 at 12:47

-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

edited Apr 07 '20 at 12:47

Venkataraman R

12,181
2
31
58

answered Apr 07 '20 at 08:39

Rishabh Singh

153
1
6

score -4 · Answer 15 · answered Jul 05 '15 at 04:31

-4

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

answered Jul 05 '15 at 04:31

Snoozy

1

Oracle Age calculation from Date of birth and Today

15 Answers15

Result : (Executed May 28, 2019)

Explanation :

AGE

Linked

Related