實際年齡(幾歲幾天): select (trunc(sysdate)-trunc(sysdate,'y')-datetime) from your_table虛年齡(幾歲): select to_char(sysdate,'yyyy')-to_char(datetime,'yyyy') real_age, --週歲 trunc(sysdate)-to_date(to_char(sysdate,'yyyy')||to_char (datetime,'mmdd'),'yyyymmdd') days --天數 from your_table
SELECT TRUNC((SYSDATE - DateTime)/365,0) FROM YOURTABLE;
我的寫錯了 再貼次實際年齡(幾歲幾天): select to_char(sysdate,'yyyy')-to_char(datetime,'yyyy') real_age, --週歲 trunc(sysdate)-to_date(to_char(sysdate,'yyyy') ¦ ¦to_char (datetime,'mmdd'),'yyyymmdd') days --天數 from your_table 虛年齡(幾歲): select to_char(sysdate,'yyyy')-to_char(datetime,'yyyy')+1 v_age from your_table
上面的算法有問題,修改如下 --實際年齡select decode( sign(dif_days), 1 ,to_char(sysdate,'yyyy')-to_char(datetime,'yyyy') , --當前日期大於生日-1天 -1,to_char(sysdate,'yyyy')-to_char(datetime,'yyyy')-1, --當前日期小於生日-1天 0 ,to_char(sysdate,'yyyy')-to_char(datetime,'yyyy') --當前日期等於生日-1天 ) year, --幾歲 abs(dif_days) days --幾天 from ( select date_time, trunc(sysdate)-to_date(to_char(sysdate,'yyyy')||to_char(datetime,'mmdd'),'yyyymmdd')+1 dif_days from your_table ) 虛年齡(幾歲):
select to_char(sysdate,'yyyy')-to_char(datetime,'yyyy')+1 v_age from your_table
select (trunc(sysdate)-trunc(sysdate,'y')-datetime) from your_table虛年齡(幾歲):
select to_char(sysdate,'yyyy')-to_char(datetime,'yyyy') real_age, --週歲
trunc(sysdate)-to_date(to_char(sysdate,'yyyy')||to_char (datetime,'mmdd'),'yyyymmdd') days --天數
from your_table
SELECT TRUNC((SYSDATE - DateTime)/365,0)
FROM YOURTABLE;
select to_char(sysdate,'yyyy')-to_char(datetime,'yyyy') real_age, --週歲
trunc(sysdate)-to_date(to_char(sysdate,'yyyy') ¦ ¦to_char (datetime,'mmdd'),'yyyymmdd') days --天數
from your_table 虛年齡(幾歲):
select to_char(sysdate,'yyyy')-to_char(datetime,'yyyy')+1 v_age
from your_table
--實際年齡select decode(
sign(dif_days),
1 ,to_char(sysdate,'yyyy')-to_char(datetime,'yyyy') , --當前日期大於生日-1天
-1,to_char(sysdate,'yyyy')-to_char(datetime,'yyyy')-1, --當前日期小於生日-1天
0 ,to_char(sysdate,'yyyy')-to_char(datetime,'yyyy') --當前日期等於生日-1天
) year, --幾歲
abs(dif_days) days --幾天
from (
select date_time,
trunc(sysdate)-to_date(to_char(sysdate,'yyyy')||to_char(datetime,'mmdd'),'yyyymmdd')+1 dif_days
from your_table
)
虛年齡(幾歲):
select to_char(sysdate,'yyyy')-to_char(datetime,'yyyy')+1 v_age
from your_table
dif_days:是指 當前年的生日和當前日期之間的差距天數
trunc(sysdate)-to_date(to_char(sysdate,'yyyy')||to_char(datetime,'mmdd'),'yyyymmdd')+1 这里为什么要+1