自己编写的计算时间差值的Oracle函数 DateDiff
/***@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秒