PL / SQL：两个日期之间的差异

admin

PL / SQL：两个日期之间的差异

sql

我编写了一个过程来查找两个日期之间的差异，并以YYYY-MM-DD格式显示结果，但是遇到错误。谁可以帮我这个事？

CREATE OR REPLACE PROCEDURE DATECALCULATOR(DATE_1 IN DATE, DATE_2 IN DATE) AS
  DATEDIFF DATE;

BEGIN
    DATEDIFF=TO_DATE(DATE_1,'YYYY-MM-DD')-TO_DATE(DATE_2,'YYYY-MM-DD');
    DBMS_OUTPUT.PUT_LINE ('Date difference is: ' || TO_DATE(DATEDIFF,'YYYY-MM-DD'));
END;
/

执行程序代码：

EXECUTE DATECALCULATOR('2017-09-10', '2010-01-01');

阅读 196

2021-07-01

共1个答案

admin

使用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;
/

2021-07-01