Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

datediff function for dates or timestamps

User_09OBWSep 13 2022

I have a function that calculates the difference between 2 dates or timestamps which is working fine.
Is there a way to modify the function to display the fractional part of the TIMESTAMP in the difference as part of the result. I want both cases to be handled in the same function if possible.


CREATE  OR REPLACE FUNCTION datediff (p_from date, p_to date)
return varchar2 is
  l_years    PLS_INTEGER;
  l_from     DATE;
  l_interval interval day(3) to second(0);
begin
  l_years := TRUNC(MONTHS_BETWEEN(p_to, p_from)/12);
  l_from := ADD_MONTHS(p_from, l_years * 12);
  l_interval := (p_to - l_from) DAY(3) TO SECOND(0);
  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;
/

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


SELECT 
datediff (TO_TIMESTAMP('1981-04-01 10:11:13.551000000', 'YYYY-MM-DD HH24:MI:SS.FF'),
TO_TIMESTAMP('2022-04-03 17:48:09.878700000', 'YYYY-MM-DD HH24:MI:SS.FF')) as  diff FROM DUAL;

/* want to show fractional difference here */

DIFF
41 Years 2 Days 7 Hours 36 Minutes 56 Seconds 
Comments
Post Details
Added on Sep 13 2022
0 comments
270 views