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

User · Answer

This seems considerably easier than what anyone else has suggested  select sysdate-to date  30-jul-1977   from dual

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

select  SYSDATE-DOB  365  Age  from dual

User · Answer

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

User · Answer

SELECT  TRUNC  SYSDATE - TO DATE DOB   YYYY-MM-DD     365 25  AS AGE TODAY FROM DUAL    This is easy and straight to the point

User · Answer

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

User · Answer

SQL gt 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

User · Answer

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

User · Answer

And an alternative without using any arithmetic and numbers  although there is nothing wrong with that    SQL gt  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

User · Answer

SQL gt  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 gt

User · Answer

Age  full years  of the Person   SELECT   TRUNC months between sysdate  per DATE OF BIRTH    12  AS  Age  FROM PD PERSONS per

[oracle] Oracle Age calculation from Date of birth and Today

Examples related to oracle

Examples related to datetime