select * from tc 1998-10-8 1998-10-19 1988-11-8 1987-10-8--SQL: select t01, case when to_number(to_char(sysdate,'dd'))-to_number(to_char(t01,'dd'))>0 or to_number(to_char(sysdate,'mm'))-to_number(to_char(t01,'mm'))>0 then to_number(to_char(sysdate,'yyyy'))-to_number(to_char(t01,'yyyy'))+1 when to_number(to_char(sysdate,'dd'))-to_number(to_char(t01,'dd'))<=0 or to_number(to_char(sysdate,'mm'))-to_number(to_char(t01,'mm'))<=0 then to_number(to_char(sysdate,'yyyy'))-to_number(to_char(t01,'yyyy')) end from tc--RESULT: 1998-10-8 11 1998-10-19 10 1988-11-8 21 1987-10-8 22
select * from tc 1998-10-8 1998-10-19 1988-11-8 1987-10-8--SQL: select t01, case when to_number(to_char(sysdate,'dd'))-to_number(to_char(t01,'dd'))>0 or to_number(to_char(sysdate,'mm'))-to_number(to_char(t01,'mm'))>0 then to_number(to_char(sysdate,'yyyy'))-to_number(to_char(t01,'yyyy'))+1 when to_number(to_char(sysdate,'dd'))-to_number(to_char(t01,'dd'))<=0 or to_number(to_char(sysdate,'mm'))-to_number(to_char(t01,'mm'))<=0 then to_number(to_char(sysdate,'yyyy'))-to_number(to_char(t01,'yyyy')) end from tc--RESULT: 1998-10-8 11 1998-10-19 10 1988-11-8 21 1987-10-8 22
create or replace function T_age(v_date date) return number is Result number; s_year number; s_month number; s_day number; t_year number; t_month number; t_day number; begin select to_number(to_char(sysdate,'yyyy')) into s_year from dual; select to_number(to_char(sysdate,'MM')) into s_month from dual; select to_number(to_char(sysdate,'DD')) into s_day from dual; select to_number(to_char(v_date,'yyyy')) into t_year from dual; select to_number(to_char(v_date,'MM')) into t_month from dual; select to_number(to_char(v_date,'DD')) into t_day from dual; Result := s_year - t_year;
if(s_month > t_month) then Result := Result; elsif(s_month = t_month) then
if (s_day >= t_day) then Result := Result; else Result := Result - 1; end if; else Result := Result - 1; end if; return(Result); end T_age; select t_age(日期字段) from 旧表; 我觉得这样是不是有点复杂了啊这样年龄是算出来了,在插入到新表中
set t_age=算出来的年龄
再
update 新表
set t_age = trunc(sysdate,'yyyy') - trunc(csrq,'yyyy')
update yourTable set t_age = round(to_number(to_char(sysdate,'yyyy-mm-dd'))-to_number(to_char(date型的年龄列,'yyyy-mm-dd')));
这个相减,然后四舍五入!就可以实现你们经理的想法是过了生日才算是长一岁,也就是不能单纯的拿年份来加减
1998-10-19
1988-11-8
1987-10-8--SQL:
select t01,
case when to_number(to_char(sysdate,'dd'))-to_number(to_char(t01,'dd'))>0
or to_number(to_char(sysdate,'mm'))-to_number(to_char(t01,'mm'))>0
then to_number(to_char(sysdate,'yyyy'))-to_number(to_char(t01,'yyyy'))+1
when to_number(to_char(sysdate,'dd'))-to_number(to_char(t01,'dd'))<=0
or to_number(to_char(sysdate,'mm'))-to_number(to_char(t01,'mm'))<=0
then to_number(to_char(sysdate,'yyyy'))-to_number(to_char(t01,'yyyy'))
end
from tc--RESULT:
1998-10-8 11
1998-10-19 10
1988-11-8 21
1987-10-8 22
1998-10-19
1988-11-8
1987-10-8--SQL:
select t01,
case when to_number(to_char(sysdate,'dd'))-to_number(to_char(t01,'dd'))>0
or to_number(to_char(sysdate,'mm'))-to_number(to_char(t01,'mm'))>0
then to_number(to_char(sysdate,'yyyy'))-to_number(to_char(t01,'yyyy'))+1
when to_number(to_char(sysdate,'dd'))-to_number(to_char(t01,'dd'))<=0
or to_number(to_char(sysdate,'mm'))-to_number(to_char(t01,'mm'))<=0
then to_number(to_char(sysdate,'yyyy'))-to_number(to_char(t01,'yyyy'))
end
from tc--RESULT:
1998-10-8 11
1998-10-19 10
1988-11-8 21
1987-10-8 22
Result number;
s_year number;
s_month number;
s_day number;
t_year number;
t_month number;
t_day number;
begin
select to_number(to_char(sysdate,'yyyy')) into s_year from dual;
select to_number(to_char(sysdate,'MM')) into s_month from dual;
select to_number(to_char(sysdate,'DD')) into s_day from dual;
select to_number(to_char(v_date,'yyyy')) into t_year from dual;
select to_number(to_char(v_date,'MM')) into t_month from dual;
select to_number(to_char(v_date,'DD')) into t_day from dual;
Result := s_year - t_year;
if(s_month > t_month) then
Result := Result;
elsif(s_month = t_month) then
if (s_day >= t_day) then
Result := Result;
else
Result := Result - 1;
end if;
else
Result := Result - 1;
end if;
return(Result);
end T_age;
select t_age(日期字段) from 旧表;
我觉得这样是不是有点复杂了啊这样年龄是算出来了,在插入到新表中