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

# 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
• 2
1 Solution

Commented:
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);
Exception
when others then
Return null;
End Show_Time;
0

Commented:
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

Commented:
Very elegant Andrew!
0

## Featured Post

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