How to Get Age in Years,Months and Days Using Oracle

How to get age in years,months and days using Oracle

Very similar to Lalit's answer, but you can get an accurate number of days without assuming 30 days per month, by using add_months to adjust by the total whole-month difference:

select sysdate,
  hiredate,
  trunc(months_between(sysdate,hiredate) / 12) as years,
  trunc(months_between(sysdate,hiredate) -
    (trunc(months_between(sysdate,hiredate) / 12) * 12)) as months,
  trunc(sysdate)
    - add_months(hiredate, trunc(months_between(sysdate,hiredate))) as days
from emp;

SYSDATE    HIREDATE        YEARS     MONTHS       DAYS
---------- ---------- ---------- ---------- ----------
2015-10-26 1980-12-17         34         10          9
2015-10-26 1981-02-20         34          8          6
2015-10-26 1981-02-22         34          8          4
2015-10-26 1981-04-02         34          6         24
2015-10-26 1981-09-28         34          0         28
2015-10-26 1981-05-01         34          5         25
2015-10-26 1981-06-09         34          4         17
2015-10-26 1982-12-09         32         10         17
2015-10-26 1981-11-17         33         11          9
2015-10-26 1981-09-08         34          1         18
2015-10-26 1983-01-12         32          9         14
2015-10-26 1981-12-03         33         10         23
2015-10-26 1981-12-03         33         10         23
2015-10-26 1982-01-23         33          9          3

You can verify by reversing the calculation:

with tmp as (
    select trunc(sysdate) as today,
      hiredate,
      trunc(months_between(sysdate,hiredate) / 12) as years,
      trunc(months_between(sysdate,hiredate) -
        (trunc(months_between(sysdate,hiredate) / 12) * 12)) as months,
      trunc(sysdate)
        - add_months(hiredate, trunc(months_between(sysdate,hiredate))) as days
    from emp
)
select * from tmp
where today != add_months(hiredate, (12 * years) + months) + days;

no rows selected

Oracle Age calculation from Date of birth and Today

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>

Oracle query to calculate current age

To get round the 21st century problem, just modifying @the_silk's answer slightly:

SELECT
  CASE WHEN SUBSTR(dob, -2, 2) > 13
  THEN FLOOR
        (
            MONTHS_BETWEEN
            (
                SYSDATE
            ,   TO_DATE(SUBSTR(dob, 1, 7) || '19' || SUBSTR(dob, -2, 2), 'DD-MON-YYYY')
            ) / 12
        )
  ELSE
       FLOOR(MONTHS_BETWEEN(sysdate,TO_DATE(dob,'DD-MON-YY'))/12)
  END
FROM
birth

Please be aware though that this assumes that any date year between '00' and '13' is 21st century, so this sql should only be used if you are building a one off throwaway script, otherwise it will become out of date and invalid before long.

The best solution would be to rebuild this table, converting the varchar column into a date column, as alluded to by Ben.

Get age from birth date PL/SQL (implicit cursor), with dbms_output.put_line

You can convert the substring you have to a date, use months_between() to get the number of months, divide that by 12, and round or (more likely) truncate that:

        dbms_output.put_line(initcap(rec.name)||', '
            ||initcap(rec.sName) ||', '
            ||trunc(months_between(sysdate, to_date(rec.newBiDate, 'YYYYMMDD'))/12, 1));

So in full, keeping your cursor as it is:

declare
cursor c_ownersList is select name, sName, substr(birthDate, 1, 8) as newBiDate
                        from owners; 
begin
    for rec in c_ownersList loop
        dbms_output.put_line(initcap(rec.name)||', '
            ||initcap(rec.sName) ||', '
            ||trunc(months_between(sysdate, to_date(rec.newBiDate, 'YYYYMMDD'))/12, 1));
    end loop;
end;
/

which gives:

John, Johnson, 55.5
Allen, Mcallen, 43.6

db<>fiddle, including a query to show the stages of the calculation.

If you want a specific decimal separator (i.e. a comma) then use to_char() with a format mask; otherwise it will use your session settings.

And, as already said, you should really not be storing dates as strings; if the -XXXX part is useful then store it separately.

Oracle PL/SQL, How to calculate age from date birth using simple substitution variable and months_between

All SQL examples from others and me are probably better thing to use unless you are learning something about PL/SQL. Here's what I came up with:

 DECLARE
  v_dob        DATE:= to_date('&v_dob', 'MM/DD/YYYY'); -- copy this line and you'll be fine
  v_your_age   NUMBER(3, 1);
BEGIN
  v_your_age := TRUNC(MONTHS_BETWEEN(SYSDATE, v_dob))/12;  
  DBMS_OUTPUT.PUT_LINE ('Your age is ' || v_your_age);
END;
/

I passed 01/01/2010. Got this in output: 

 Your age is 3.1

Another SQL example:

SELECT empno, ename, hiredate
    , TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12) years_of_service  
 FROM scott.emp
/

---

Related Topics