Have Geek Brain

SQL query question

I am using the instruction below to generate a person's age 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 so far, that I needed an expression that could count months and even days to get the age.

+2


source to share


3 answers


Googling for 'oracle get age from dob' returns multiple 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.

+3


source


You can use EXTRACT function like

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

Replace YEAR with whatever you need.

/ edit I think I misunderstood. If you need higher precision, perhaps Intervals can help ( http://blagispat.blogspot.com/2007/11/heres-short-article-on-using-intervals.html ). (Sry, but new users can only submit 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.

+1


source


Oracle supports arithmetic operations directly on columns DATE

:

SELECT SYSDATE - BIRTH_DATE FROM NAME WHERE NAME_ID =NAME_ID

As a result, there will be a number that expresses the difference in days.

If you want for a few months use this:

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

If you want for several years, divide MONTHS_BETWEEN

by 12.

0


source






More articles:


All Articles