Oracle中怎样获得两个date型数据之间的年份差

Question

欲创建一个触发器当插入一条记录到users表或更改users的一条记录的birthday（date型）的值时触发器将系统时间的年减去userbirthday生日的年份赋值给年龄age这个字段应该怎样创建这个触发器谢谢

Answer 1

Create or replace trigger biud_users_birthday
  Before insert or update 
   On users
 Declare
 age users.age%type;
 Begin
       select trunc(sysdate-birthday,'yy') 
         into age
         from users  WHERE 语句;
    merge into....--实现插入或者更新 
  else
   raise_application_error(-20001,’BIRTHERDAY DATA error.’);
 End;
 10.--创建触发器      
11.create or replace trigger book_trigger       
12.before insert on book       
13.for each row       
14.begin       
15.select book_seq.nextval into :new.bookId from dual;      
16.end ;   

Answer 2

1楼您好
Begin
       select trunc(sysdate-birthday,'yy') 
         into age
         from users  WHERE 语句;
    merge into....--实现插入或者更新trunc截取的时间格式得出‘yy’，好像时间格式‘yyyy’才对吧

Answer 3

select trunc(sysdate-birthday,'yy') 
这个句子需要改正一下
select to_char(sysdate,'yyyy')-to_char(birthday,'yyyy') ....

Answer 4

-- 两种方法，有细微的差别，楼主聪明人，一看便知!
-- 具体用哪一种方法，关键得按楼主的规则办事!
SELECT empno, ename,
       TO_CHAR(hiredate,'YYYY-MM-DD') AS hiredate,
       TO_CHAR(sysdate,'YYYY-MM-DD') AS sysdates,
       TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) Year1, 
       TO_CHAR(sysdate,'YYYY')-TO_CHAR(hiredate,'YYYY') Year2
FROM emp;-----------------------------------------------------------------------------------------------
     EMPNO ENAME                HIREDATE             SYSDATES                  YEAR1      YEAR2
---------- -------------------- -------------------- -------------------- ---------- ----------
      7876 ADAMS                1987-05-23           2010-05-17                   22         23
      7521 WARD                 1981-02-22           2010-05-17                   29         29
      7566 JONES                1981-04-02           2010-05-17                   29         29
      7499 ALLEN                1981-02-20           2010-05-17                   29         29
      7839 KING                 1981-11-17           2010-05-17                   28         29
      7902 FORD                 1981-12-03           2010-05-17                   28         29
      7698 BLAKE                1981-05-01           2010-05-17                   29         29
      7844 TURNER               1981-09-08           2010-05-17                   28         29
      7369 SMITH                1980-12-17           2010-05-17                   29         30
      7900 JAMES                1981-12-03           2010-05-17                   28         29
      7934 MILLER               1982-01-23           2010-05-17                   28         28
      7782 CLARK                1981-06-09           2010-05-17                   28         29
      7788 SCOTT                1987-04-19           2010-05-17                   23         23
      7654 MARTIN               1981-09-28           2010-05-17                   28         29已选择14行。

Answer 5

SELECT D1, D2, DECODE(MOD(MONTHS, 12), 0, MONTHS / 12, trunc(MONTHs / 12) )||'年'||MOD(MONTHS, 12)||'月'
  FROM (SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20070217', 'yyyymmdd'), ROWNUM),
                       'YYYY-MM') D1,
               TO_CHAR(SYSDATE, 'yyyy-mm') D2,
               MONTHS_BETWEEN(SYSDATE,
                              ADD_MONTHS(TO_DATE('20070217', 'yyyymmdd'),
                                         ROWNUM)) + 1 MONTHS
          FROM DUAL
        CONNECT BY ROWNUM <=
                   MONTHS_BETWEEN(SYSDATE, TO_DATE('20070217', 'yyyymmdd')))

Answer 6

select months_between(to_char(birthday,'yyyy'),to_char(sysdate,'yyyy')) from table

Answer 7

按你的描述应该是CREATE OR REPLACE TRIGGER tg_users_age BEFORE
INSERT OR UPDATE ON users
FOR EACH ROW
WHEN (OLD.birthday IS NULL OR NEW.birthday<>OLD.birthday)
BEGIN
SELECT EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM :NEW.birthday)
INTO :NEW.age FROM dual;
END;
用trunc(months_between(sysdate,birthday)/12)来计算年龄应该更合理点

Answer 8

create or replace trigger p_age before insert or update on users for each row
begin
select (to_char(sysdate,'yyyy')-to_char(:new.userbirthday,'yyyy')) into :new.age
from dual;
end