PL/SQL:两个日期之间的差异 [英] PL/SQL: Difference between two dates

问题描述

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

I've written a procedure to find the difference between two dates and display the result in YYYY-MM-DD format but encountered an error. Can anyone help me on this?

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');

推荐答案

使用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的差异:

Or, use the MONTHS_BETWEEN function to get the difference in the format 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;
/

这篇关于PL/SQL:两个日期之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！