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.
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;
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
This seems considerably easier than what anyone else has suggested
select sysdate-to_date('30-jul-1977') from dual;
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.
SELECT
TRUNC((SYSDATE - TO_DATE(DOB, 'YYYY-MM-DD'))/ 365.25) AS AGE_TODAY FROM DUAL;
This is easy and straight to the point.
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
select (SYSDATE-DOB)/365 "Age" from dual
Age (full years) of the Person:
SELECT
TRUNC(months_between(sysdate, per.DATE_OF_BIRTH) / 12) AS "Age"
FROM PD_PERSONS per
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
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;
AGE
----------
22
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)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.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.
Source: Stackoverflow.com