3 Replies Latest reply: Nov 20, 2012 7:11 AM by Nimish Garg RSS

    calculate the no. of hours

    user12957777
      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
          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
            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
              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