My Experiences with Databases

Oracle,MySQL,SQL SERVER,Python,Azure,AWS,Oracle Cloud,GCP Etc

  • Enter your email address to follow this blog and receive notifications of new posts by email.

  • Total Views

    • 498,705 hits
  • $riram $anka


    The experiences, Test cases, views, and opinions etc expressed in this website are my own and does not reflect the views or opinions of my employer. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.Product and company names mentioned in this website may be the trademarks of their respective owners.

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>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: