This discussion is archived
3 Replies Latest reply: Nov 20, 2012 5:11 AM by Nimish Garg RSS

calculate the no. of hours

user12957777 Newbie
Currently Being Moderated
I have table named as ENTRY as follows:
ENTRY
---------
ID       NAME    FOR_DT          IN_OUT    TIME
1         AAA     11-20-2012      IN            20-11-2012 09:20:42 AM
2         BBB      11-20-2012      OUT         20-11-2012 09:20:42 AM

How to calculate the difference in hours for the NAME AAA and BBB for particular date i.e FOR_DT ?

Note :- The difference will be the no. of hours AAA has performed his duty for the date FOR_DT

Sanjay
  • 1. Re: calculate the no. of hours
    hm Expert
    Currently Being Moderated
    When you substract two date values you will get the difference in days.

    So when you need the difference in hours you can use:
    hours:= (dt1 - dt2) * 24;
    hm
  • 2. Re: calculate the no. of hours
    Hoek Guru
    Currently Being Moderated
    There are several ways to calculate time differences.
    See:
    http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129
    for a 'classic example'.
    Other functions that you can use are EXTRACT and /or NUMTODSINTERVAL.
    Search this forum for many examples.

    Too bad you did not post create table and insert into statement, since now we do not know the datatype of your column.

    Suggest to read and search the following links, to find more explanations and examples:
    {message:id=9360002}
    http://www.oracle.com/pls/db112/homepage (search box = at upper left corner)
  • 3. Re: calculate the no. of hours
    Nimish Garg Guru
    Currently Being Moderated
    Try this
    SELECT
         A.NAME,
         A.FOR_DT,
         A.TIME,
         B.NAME,
         B.FOR_DT,
         B.TIME,
         (A.TIME - B.TIME)*24 DIFF_HRS
    FROM
         MY_TABLE A,
         MY_TABLE B
    WHERE
         A.NAME='AAA'
         AND B.NAME='BBB'
         AND A.FOR_DT=B.FOR_DT

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points