oracle 10g 触发器中时间相减问题

Question

本帖最后由 snreo 于 2011-11-24 16:09:48 编辑

Answer 1

SQL> select trunc(extract(day from dt2-dt1)/365,1)
  2  from (
  3  select to_timestamp('2008-12-10','yyyy-mm-dd hh24:mi:ss') dt1,
  4         to_timestamp('2011-11-10 18:54:25','yyyy-mm-dd hh24:mi:ss') dt2
  5  from dual)
  6  /
 
TRUNC(EXTRACT(DAYFROMDT2-DT1)/
------------------------------
                           2.9

Answer 2

SELECT floor(months_between(a,b)/12) AS 年,
       MOD(months_between(a,b),12) AS 月
FROM
(
SELECT trunc(to_date('2011-11-10 18:54:25','yyyy-mm-dd hh24:mi:ss'),'mm')a,
trunc(to_date('2008-12-10','yyyy-mm-dd'),'mm') b
FROM dual
)

Answer 3

SELECT replace(trunc(months_between(a,b)/12,1),'.','年')||'月'
FROM
(
SELECT trunc(to_date('2011-11-10 18:54:25','yyyy-mm-dd hh24:mi:ss'),'mm')a,
trunc(to_date('2008-12-10','yyyy-mm-dd'),'mm') b
FROM dual
)--result:
2年9月

Answer 4

那如何把他加到 触发器里
create or replace trigger insert_out_user
  after delete on employee
  for each row
declare
 v_num number;
begin  select count(*) into v_num from out_employee;  insert into out_employee
  values
    (
    v_num + 1,
     :old.u_name,
     :old.u_entry_time,
     sysdate,
     
     select trunc(extract(day from dt2 - dt1) / 365, 1)
     from
     (
     select to_timestamp(:old.u_entry_time, 'yyyy-mm-dd hh24:mi:ss') dt1,
            to_timestamp(sysdate, 'yyyy-mm-dd hh24:mi:ss') dt2
     from dual),
     
     :old.u_department,
     :old.u_post,
     :old.u_sex,
     :old.u_age,
     :old.u_phone
     );  end insert_out_user;

Answer 5

SELECT replace(trunc(months_between(a,b)/12,1),'.','年')||'月'
FROM
(
SELECT trunc(to_date('2011-11-10 18:54:25','yyyy-mm-dd hh24:mi:ss'),'mm')a,
trunc(to_date('2008-12-10','yyyy-mm-dd'),'mm') b
FROM dual
)

Answer 6

create or replace trigger insert_out_user
  after delete on employee
  for each row
declare
 v_num number;
begin  select count(*) into v_num from out_employee;  insert into out_employee
  values
    (
    v_num + 1,
     :old.u_name,
     :old.u_entry_time,
     sysdate,
     
     select trunc(extract(day from dt2 - dt1) / 365, 1)
     from
     (
     select to_timestamp(:old.u_entry_time, 'yyyy-mm-dd hh24:mi:ss') dt1,
            to_timestamp(sysdate, 'yyyy-mm-dd hh24:mi:ss') dt2
     from dual),
     
     :old.u_department,
     :old.u_post,
     :old.u_sex,
     :old.u_age,
     :old.u_phone
     );  end insert_out_user;

Answer 7

select  round((months_between(to_date('2011-11-10', 'yyyy-mm-dd'),
to_date('2008-12-10', 'yyyy-mm-dd')))/12, 1) as months from dual;