创建oracle函数对日期数据进行相关格式转换处理:
1.日期字符串转换秒数
CREATE OR REPLACE FUNCTION F_CHAR_TO_SEC(DATE_STR IN VARCHAR2, FMT IN VARCHAR2 :='YYYY-MM-DD') RETURN NUMBER IS
RESULT NUMBER(11):=0;
BEGIN
RESULT := TO_NUMBER(TO_DATE(DATE_STR,FMT) - TO_DATE('1970-01-01 08:00:00','YYYY-MM-DD HH24:MI:SS'))*24*60*60;
RETURN(RESULT);
END FUNC_CHAR_TO_SEC;
/
2.日期类型转换秒数
CREATE OR REPLACE FUNCTION F_DATE_TO_SEC(PARAM_DATE IN DATE) RETURN NUMBER IS
RESULT NUMBER;
BEGIN
RESULT := TO_NUMBER(PARAM_DATE - TO_DATE('1970-01-01 08:00:00','YYYY-MM-DD HH24:MI:SS'))*24*60*60;
RETURN(RESULT);
END FUNC_DATE_TO_SEC;
/
3.秒数转换日期类型
CREATE OR REPLACE FUNCTION F_SEC_TO_DATE(SEC_NUM IN NUMBER) RETURN DATE IS
RESULT DATE := NULL;
BEGIN
RESULT := TO_DATE('1970-01-01 08:00:00','YYYY-MM-DD HH24:MI:SS')+(SEC_NUM/(24*3600));
RETURN(RESULT);
END FUNC_SEC_TO_DATE;
/
4.秒数转换字符串类型
CREATE OR REPLACE FUNCTION F_SEC_TO_CHAR(SEC_NUM IN NUMBER,FMT IN VARCHAR2:='YYYY-MM-DD') RETURN VARCHAR2 IS
RESULT VARCHAR2(50):=NULL;
BEGIN
RESULT := TO_CHAR(F_SEC_TO_DATE(SEC_NUM),FMT);
RETURN(RESULT);
END FUNC_SEC_TO_CHAR;
/