Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

help me to get oracle query

How to write oracle query to find the time difference between two date fields (on same day in a  table) in Hour,Minute,Second pattern format

For Example Two date-fields like   indatetime & processeddatetime on same date with different time periods.( Not Month_between function with to_char function returns some decimal values)
---Help in getting Hour:Minute:Second pattern
0
balaganesh
Asked:
balaganesh
  • 2
1 Solution
 
venkotchCommented:
Have something like this:

Function Show_Time_Diff(p_start_time in date, p_end_date in date) IS
  ss  number;
  mi  number;
  hh  number;
Begin
  ss := (p_End_Time - P_Start_Time)*24*3600;
  hh := trunc(ss/3600);
  mi := trunc((ss-(hh*3600))/60);
  ss := round(ss -(hh*3600) - mi*60);
  Return 'Time: '||lpad(hh,2,'0')||':'||
         lpad(mi,2,'0')||':'||lpad(ss,2,'0');
  Exception
    when others then
      Return null;
End Show_Time;
0
 
andrewstCommented:
SQL> create table t (indatetime date, processeddatetime date)
  2  /

Table created.

SQL> insert into t values
  2  ( to_date('01-JAN-2003 09:30:01','DD-MON-YYYY HH24:MI:SS')
  3  , to_date('01-JAN-2003 17:00:59','DD-MON-YYYY HH24:MI:SS')
  4* );

1 row created.

SQL> select to_char( trunc(sysdate)+(processeddatetime-indatetime), 'HH24:MI:SS' ) timediff
  2* from t;

TIMEDIFF
--------
07:30:58
0
 
venkotchCommented:
Very elegant Andrew!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now