help me to get oracle query

Posted on 2003-03-28
Medium Priority
Last Modified: 2008-02-20
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
Question by:balaganesh
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
  • Learn & ask questions
  • 2

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


Expert Comment

ID: 8226597
Very elegant Andrew!

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question