Oracle datediff function-CodePudding

I have a function that will calculate the difference between 2 dates, which appears to work fine when the smaller value is passed as the first parameter.

Is there a way that I can modify the function so it doesn't matter, which value is passed in first? I saw something about a least() and greatest(). Can someone help me out? Thanks to all who answer.


CREATE  OR REPLACE FUNCTION datediff (p_from timestamp, p_to timestamp)
return varchar2 is
  l_years    PLS_INTEGER;
  l_from     TIMESTAMP;
  l_interval interval day(3) to second(6);
begin
  l_years    := TRUNC(MONTHS_BETWEEN(p_to, p_from)/12);
  l_from     := CAST(TRUNC(ADD_MONTHS(p_from, l_years * 12), 'MI') AS TIMESTAMP)
                    NUMTODSINTERVAL( EXTRACT(SECOND FROM p_from), 'SECOND' );
  l_interval := (p_to - l_from) DAY(3) TO SECOND(6);
  return l_years || ' Years '
    || extract (day from l_interval) || ' Days '
    || extract (hour from l_interval) || ' Hours '
    || extract (minute from l_interval) || ' Minutes '
    || extract (second from l_interval) || ' Seconds';
end datediff;
/

 Works fine

SELECT 
datediff( TO_DATE('1981-04-01 10:11:13','YYYY-MM-DD HH24:MI:SS'), 
TO_DATE('2022-04-03 17:48:09','YYYY-MM-DD HH24:MI:SS')) as  diff FROM DUAL;

DIFF
41 Years 2 Days 7 Hours 36 Minutes 56 Seconds

/* want same result as above*/

SELECT 
datediff( TO_DATE('2022-04-03 10:11:13','YYYY-MM-DD HH24:MI:SS'), 
TO_DATE('1981-04-01 17:48:09','YYYY-MM-DD HH24:MI:SS')) as  diff FROM DUAL

CodePudding user response：

Exactly as you said, use LEAST and GREATEST:

CREATE FUNCTION datediff (p_from timestamp, p_to timestamp)
return varchar2 is
  v_from     TIMESTAMP := LEAST(p_from, p_to);
  v_to       TIMESTAMP := GREATEST(p_from, p_to);
  l_years    PLS_INTEGER;
  l_from     TIMESTAMP;
  l_interval interval day(3) to second(6);
begin
  l_years    := TRUNC(MONTHS_BETWEEN(v_to, v_from)/12);
  l_from     := CAST(TRUNC(ADD_MONTHS(v_from, l_years * 12), 'MI') AS TIMESTAMP)
                    NUMTODSINTERVAL( EXTRACT(SECOND FROM v_from), 'SECOND' );
  l_interval := (v_to - l_from) DAY(3) TO SECOND(6);
  return l_years || ' Years '
    || extract (day from l_interval) || ' Days '
    || extract (hour from l_interval) || ' Hours '
    || extract (minute from l_interval) || ' Minutes '
    || extract (second from l_interval) || ' Seconds';
end datediff;
/