#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    3
    Rep Power
    0

    Please help with the query.


    Hello. Can anyone help with the following issue..

    I have two times- start time and finish time represented in integers.
    For example, 1145 and 1205.
    I need to create function which will substract finish time from start time and represent the resut in hours.

    I have no idea how to do it.
    If anyone can help, please show me how this can be done.

    Thanks in advance.
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388
    Originally Posted by garryBrown
    Hello.
    ...
    I have no idea how to do it.
    Try:
    Code:
    SQL> SELECT NUMTODSINTERVAL ( ( (TO_DATE ( 1205, 'HH24MI') - TO_DATE ( 1145, 'HH24MI')) * 24), 'HOUR')
      2*   FROM DUAL
    SQL> /
    
    NUMTODSINTERVAL(((TO_DATE(1205,'HH24MI')-TO_DATE(1145,'HH24MI'))*24),'HOUR'
    ---------------------------------------------------------------------------
    +000000000 00:20:00.000000000
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    3
    Rep Power
    0
    Thanks for query. It's working perfectly. would you know how to make a function from this query? I ALSO NEED THE RESULT TO BE DISPLAYED LIKE THAT: 02:00.

    For example, i created the following function:

    CREATE OR REPLACE FUNCTION calc_volume(
    height IN number, length IN number, width IN number)
    RETURN NUMBER IS
    BEGIN
    RETURN (height * length * width);
    END;

    What I need is time function constructed in the same way.

    Thanks in advance.
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Talking


    Originally Posted by garryBrown
    Thanks for query. It's working perfectly. would you know how to make a function from this query? I ALSO NEED THE RESULT TO BE DISPLAYED LIKE THAT: 02:00.
    Try this:
    Code:
    CREATE OR REPLACE FUNCTION Calc_Time 
       ( Start_Time IN NUMBER, End_Time IN NUMBER)
       RETURN VARCHAR2
    IS
       Out_Time       VARCHAR2 (32);
    BEGIN
       SELECT NUMTODSINTERVAL ( ( (TO_DATE ( End_Time, 'HH24MI') 
                                 - TO_DATE ( Start_Time, 'HH24MI')) * 24), 'HOUR')
         INTO Out_Time
         FROM DUAL;
    
       RETURN SUBSTR ( Out_Time, 12, 5);
    END;
    /

IMN logo majestic logo threadwatch logo seochat tools logo