CODEWITHSUNDEEP
sqloracle-database
Ameer
Ameer

Reputation: 1028

Question About SQL Query

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.

Upvotes: 2

Views: 259

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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.

Upvotes: 0

robsn
robsn

Reputation: 744

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.

Upvotes: 1

Unreason
Unreason

Reputation: 12704

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.

Upvotes: 3

Related Questions