Date Difference(datediff) Using Oracle SQL – By Thomas Kyte(ASKTOM)
Posted by Sriram Sanka on September 11, 2022
Its an Old Good Script from my Script Collection to find the number of minutes/Seconds/Hours between two dates- Original Author – TomKyte.
create or replace function datediff( p_what in varchar2,
p_d1 in date,
p_d2 in date ) return number
as
l_result number;
begin
select (p_d2-p_d1) *
decode( upper(p_what),
'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )
into l_result from dual;
return l_result;
end;
/
WITH data
AS (SELECT To_date(SYSDATE - 2, 'dd-mon-yyyy hh24:mi:ss') d1,
To_date(SYSDATE, 'dd-mon-yyyy hh24:mi:ss') d2
FROM dual)
SELECT Datediff('ss', d1, d2) AS seconds,
Datediff('mi', d1, d2) AS minutes,
Datediff('hh', d1, d2) hours
FROM data
SQL> WITH data
2 AS (SELECT To_date(SYSDATE - 2, 'dd-mon-yyyy hh24:mi:ss') d1,
3 To_date(SYSDATE, 'dd-mon-yyyy hh24:mi:ss') d2
4 FROM dual)
5 SELECT Datediff('ss', d1, d2) AS seconds,
6 Datediff('mi', d1, d2) AS minutes,
7 Datediff('hh', d1, d2) hours
8 FROM data ;
SECONDS MINUTES HOURS
---------- ---------- ----------
172800 2880 48
SQL>
My Experiences with Databases & More 
Leave a Reply