/**
*@author:wangxl
*计算两个时间的差值
*/
CREATE OR REPLACE FUNCTION DATEDIFF(I_DATE1 IN VARCHAR, I_DATE2 VARCHAR)
? RETURN VARCHAR AS
? TOTAL_SECONDS NUMBER;
? O_RESULT????? VARCHAR2(50);
? DAYS????????? NUMBER;
? HOURS???????? NUMBER;
? MINUTES?????? NUMBER;
? SECONDS?????? NUMBER;
BEGIN
? IF (I_DATE1='SYSDATE') THEN
???? SELECT CEIL((SYSDATE -
????????????? TO_DATE(I_DATE2, 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60)
????? INTO TOTAL_SECONDS
????? FROM DUAL;
??? ELSE
?????? SELECT CEIL((TO_DATE(I_DATE1, 'yyyy-mm-dd hh24-mi-ss') -
????????????? TO_DATE(I_DATE2, 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60)
??? INTO TOTAL_SECONDS
??? FROM DUAL;
? END IF;

? DBMS_OUTPUT.PUT_LINE(TOTAL_SECONDS);
? DAYS??? := FLOOR(TOTAL_SECONDS / (24 * 3600));
? HOURS?? := FLOOR(TOTAL_SECONDS MOD(24 * 3600) / 3600);
? MINUTES := FLOOR(TOTAL_SECONDS MOD 3600 / 60);
? SECONDS := FLOOR(TOTAL_SECONDS MOD 60 );
/*
? DBMS_OUTPUT.PUT_LINE(DAYS);
? DBMS_OUTPUT.PUT_LINE(HOURS);
? DBMS_OUTPUT.PUT_LINE(MINUTES);
? DBMS_OUTPUT.PUT_LINE(SECONDS);
*/

? IF (DAYS > 0) THEN
??? O_RESULT := DAYS || '天' || HOURS || '小时' || MINUTES || '分' || SECONDS || '秒';
? ELSE
??? IF (DAYS = 0) THEN
????? O_RESULT := HOURS || '小时' || MINUTES || '分' || SECONDS || '秒';
??? END IF;
??? IF (DAYS = 0 AND HOURS = 0) THEN
????? O_RESULT := MINUTES || '分' || SECONDS || '秒';
??? END IF;
? END IF;
? RETURN O_RESULT;
END;

?

调用方法：

1，传入系统时间和某个日期时间

SQL> SELECT datediff('sysdate','2011-08-16 12:11:34') datediff from dual;
?
DATEDIFF
--------------------------------------------
21小时7分26秒

2，传入两个varchar型的时间（注意：第一个时间要比第二个时间大）

?
SQL> SELECT datediff('2011-09-12 12:21:31','2011-08-16 12:11:34') datediff from dual;
?
DATEDIFF
--------------------------------------------
27天0小时9分57秒

?