在sql中求年龄的算法

Question

select to_char(sysdate,'yyyy')-to_char(col_date,'yyyy') age from tbname;

Answer 1

select trunc(months_between(sysdte,col_date)/12) year,mod(months_between(sysdte,col_date),12) month
from tbname;如果不是很精确的话
select instr(to_char(to_date('2000','yyyy')+sysdate-col_date,'yyyy-mm-dd'),2) from tbname;

Answer 2

select (to_char(sysdate,'yyyy')-to_char(to_date('1980-08-22','yyyy-mm-dd'),'yyyy'))||to_char(sysdate,'mm')-to_char(to_date('1980-08-22','yyyy-mm-dd'),'mm')||to_char(sysdate,'dd')-to_char(to_date('1980-08-22','yyyy-mm-dd'),'dd') from dual

Answer 3

谢谢bzszp(SongZip) , liuyi8903(西西) 但还是满足不了我的要求.
如果年龄小于一岁要得出几个月零几天关键是天如何算,本人不才不得知.

Answer 4

select trunc(months_between(sysdte,col_date)/12) year,mod(months_between(sysdte,col_date),12) month,trunc((months_between(sysdte,col_date)-trunc(months_between(sysdte,col_date)))*31) day
from tbname;

Answer 5

12:53:59 SQL> select * from tz;COL_DATE
----------
1999-05-02已用时间:  00: 00: 00.16
12:54:13 SQL> select trunc(months_between(sysdate,col_date)/12) year,
12:54:16   2  mod(trunc(months_between(sysdate,col_date)),12) month,
12:54:24   3   trunc((months_between(sysdate,col_date)-trunc(months_between(sysdate,col_date)))*31)
12:54:27   4   day from tz;      YEAR      MONTH        DAY
---------- ---------- ----------
         5          5         20已用时间:  00: 00: 00.15
12:54:28 SQL>

Answer 6

select decode(trunc(months_between(sysdate,dPatientBirthday)/12),0,'',
                trunc(months_between(sysdate,dPatientBirthday)/12)||'岁') ||
       decode(mod(months_between(sysdate,dPatientBirthday),12),0,'',
          trunc(mod(months_between(sysdate,dPatientBirthday),12))||'个月') ||
   decode(trunc(sysdate - ADD_MONTHS(dPatientBirthday,months_between(sysdate,dPatientBirthday))),0,'',
          trunc(sysdate - ADD_MONTHS(dPatientBirthday,months_between(sysdate,dPatientBirthday)))||'天')   sAge
  from y_inhosinformation 
ok!