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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
259 views