sorry! select to_char(sysdate,'yyyy')-to_char(出生日期,'yyyy') as age from tbname;
SQL> select sysdate from dual;SYSDATE ---------- 06-6月 -05 /*计算年龄的过程,精确到天*/ SQL> DECLARE 2 PROCEDURE countOld (birthday IN DATE DEFAULT '1900-1-1') AS 3 num int:=0; 4 lp int:=0; 5 BEGIN 6 lp:=months_between(SYSDATE,birthday); 7 WHILE lp>0 8 LOOP 9 lp:=lp-12; 10 IF lp>0 THEN 11 num:=num+1; 12 ELSIF lp=0 THEN 13 IF to_number(substr(to_char(SYSDATE,'yyyymmdd'),7,2))>=to_number(substr(to_char(birthday,'yyyymmdd'),7,2)) THEN 14 num:=num+1; 15 END IF; 16 END IF; 17 END LOOP; 18 DBMS_OUTPUT.PUT_LINE('Your birthday are ' || to_char(birthday) || '.'); 19 DBMS_OUTPUT.PUT_LINE('You are ' || to_char(num) || '.' || chr(10)); 20 END countOld; 21 BEGIN 22 countOld(to_date('2005-1-1','yyyy-mm-dd')); 23 countOld(to_date('2004-1-1','yyyy-mm-dd')); 24 countOld(to_date('2004-6-6','yyyy-mm-dd')); 25 countOld(to_date('2004-6-7','yyyy-mm-dd')); 26 countOld(to_date('1983-3-8','yyyy-mm-dd')); 27 END; 28 / Your birthday are 01-1月 -05. You are 0. Your birthday are 01-1月 -04. You are 1. Your birthday are 06-6月 -04. You are 1. Your birthday are 07-6月 -04. You are 0. Your birthday are 08-3月 -83. You are 22.PL/SQL 过程已成功完成。SQL>
select to_char(sysdate,'yyyy')-to_char(rq,'yyyy') as age from table
select trunc(MONTHS_BETWEEN(sysdate,to_date(birthday,'yyyy/mm/dd'))/12) as age from tablename;
select to_char(sysdate,'yyyy')-to_char(出生日期,'yyyy') as age from tbname;
----------
06-6月 -05
/*计算年龄的过程,精确到天*/
SQL> DECLARE
2 PROCEDURE countOld (birthday IN DATE DEFAULT '1900-1-1') AS
3 num int:=0;
4 lp int:=0;
5 BEGIN
6 lp:=months_between(SYSDATE,birthday);
7 WHILE lp>0
8 LOOP
9 lp:=lp-12;
10 IF lp>0 THEN
11 num:=num+1;
12 ELSIF lp=0 THEN
13 IF to_number(substr(to_char(SYSDATE,'yyyymmdd'),7,2))>=to_number(substr(to_char(birthday,'yyyymmdd'),7,2)) THEN
14 num:=num+1;
15 END IF;
16 END IF;
17 END LOOP;
18 DBMS_OUTPUT.PUT_LINE('Your birthday are ' || to_char(birthday) || '.');
19 DBMS_OUTPUT.PUT_LINE('You are ' || to_char(num) || '.' || chr(10));
20 END countOld;
21 BEGIN
22 countOld(to_date('2005-1-1','yyyy-mm-dd'));
23 countOld(to_date('2004-1-1','yyyy-mm-dd'));
24 countOld(to_date('2004-6-6','yyyy-mm-dd'));
25 countOld(to_date('2004-6-7','yyyy-mm-dd'));
26 countOld(to_date('1983-3-8','yyyy-mm-dd'));
27 END;
28 /
Your birthday are 01-1月 -05.
You are 0.
Your birthday are 01-1月 -04.
You are 1.
Your birthday are 06-6月 -04.
You are 1.
Your birthday are 07-6月 -04.
You are 0.
Your birthday are 08-3月 -83.
You are 22.PL/SQL 过程已成功完成。SQL>