Question About SQL Query

Question

I am using the below statement to generate age of a person in Oracle SQL and my question is below.

SELECT TO_NUMBER(TO_CHAR(CURRENT_DATE,'YYYY'))-TO_NUMBER(TO_CHAR(BIRTH_DATE,'YYYY')) 
FROM NAME WHERE NAME_ID =NAME_ID

This statement is only correct upto so far that I need a statement which could count months and even days in order to get the age.

Answer 1

You could use the EXTRACT function like

SELECT EXTRACT( YEAR FROM( CURRENT_DATE - BIRTH_DATE )) FROM ...

Substitute YEAR by whatever you need.

/edit I think I misread. If you need higher precisions maybe Intervals could help (http://blagispat.blogspot.com/2007/11/heres-short-article-on-using-intervals.html). (Sry but new users can only post one hyperlink).

SELECT EXTRACT( YEAR FROM( CURRENT_DATE - BIRTH_DATE) YEAR TO MONTH ) FROM ...

or

SELECT EXTRACT( DAY FROM( CURRENT_DATE - BIRTH_DATE) DAY TO SECOND ) FROM ...

which returns days.

Answer 2

Googling for 'oracle get age from dob' returns several answers

select trunc((months_between(sysdate, dob))/12) age
from name;

looks like a good solution (trunc is optional) and

select to_number(to_char(sysdate,'YYYY')) - to_number(to_char(bth_date,'YYYY'))  +
       decode(sign(to_number(to_char(sysdate,'MMDD')) - 
                   to_number(to_char(bth_date,'MMDD'))),-1,-1,0) age
from name;

is also correct.

Answer 3

Oracle supports arithmetic operations directly on DATE columns:

SELECT SYSDATE - BIRTH_DATE FROM NAME WHERE NAME_ID =NAME_ID

The result here will be a number which expresses the difference in days.

If you want it in months, use this:

SELECT MONTHS_BETWEEN(SYSDATE, BIRTH_DATE) FROM NAME...

If you want it in years, divide MONTHS_BETWEEN by 12.