使用INTERVAL DAY TO SECOND
类型获取格式为+D HH24:MI:SS.FF6
:
CREATE OR REPLACE PROCEDURE DATECALCULATOR(
DATE_1 IN DATE,
DATE_2 IN DATE
) AS
DATEDIFF INTERVAL DAY(9) TO SECOND;
BEGIN
DATEDIFF= (DATE_1- DATE_2) DAY(9) TO SECOND;
DBMS_OUTPUT.PUT_LINE('Date difference is: ' || DATEDIFF );
END;
/
或者,使用MONTHS_BETWEEN
函数来获取格式上的差异YYYY-MM-DD
:
CREATE OR REPLACE PROCEDURE DATECALCULATOR(
range_end IN DATE,
range_start IN DATE
) AS
DIFF NUMBER := TRUNC( MONTHS_BETWEEN( DATE_1, DATE_2 ) ) - 1;
dt DATE := ADD_MONTHS( range_start, diff );
d INTEGER := TRUNC( range_end - dt );
dy INTEGER := EXTRACT( DAY FROM LAST_DAY( dt ) );
m INTEGER;
y INTEGER;
BEGIN
IF d > dy THEN
diff := diff + 1;
d := d - dy;
END IF;
m := MOD( TRUNC( diff ), 12 );
y := TRUNC( diff / 12 );
DBMS_OUTPUT.PUT_LINE('Date difference is: '
|| TO_CHAR( y, '0009' ) || '-' || TO_CHAR( m, 'FM09' ) || '-' || TO_CHAR( d, 'FM09' )
);
END;
/