Solved

# help me to get oracle query

Posted on 2003-03-28
Medium Priority
535 Views
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
Question by:balaganesh
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 6

Expert Comment

ID: 8224450
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

LVL 15

Accepted Solution

andrewst earned 200 total points
ID: 8224580
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

LVL 6

Expert Comment

ID: 8226597
Very elegant Andrew!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
###### Suggested Courses
Course of the Month12 days, 20 hours left to enroll