Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2011
    Posts
    11

    Unanswered: how to subtract one date from another to get hours and time

    Hi there .. Please help.

    I have 2 date fields as below.

    DATE1 DATE2
    ----------------- -----------------
    21062011:14:00:00 21062011:16:55:00
    21062011:07:00:00 21062011:16:50:00

    I want to subtract date2 from date1 to get the below results basically on time:
    2.55
    9.50

    I want to deduct time from another field to get the result in hours and minutes by using sql command in oracle

    Any urgent help will be greatly appreciated.
    Thanks x 1000 times in advance.

    Regards,
    Mack

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Just subtract the two dates. The result is the difference in days, so you just need to multiply that with 24 to get hours.

    Code:
    SELECT (date1 - date2) * 24
    FROM your_table

  3. #3
    Join Date
    Jun 2011
    Posts
    11

    how to subtract 2 date fields to get hours and minutes

    Dear Shammat,

    Thank you for your reply. I really appreciate for your help. But the result is not what is actully desired. There is a slight variation in the result.

    The 2 fields start_time & end_time are date fields with time and I just want to deduct the end_time from start_time to get in hours & minutes.

    select to_char(start_time, 'HH24:MIS'), to_char(end_time, 'HH24:MIS'), (end_time-start_time)*24 from come_leav;

    TO_CHAR( TO_CHAR( (END_TIME-START_TIME)*24
    -------- -------- ------------------------
    14:00:00 16:55:00 2.9166667
    07:00:00 16:50:00 9.8333333
    07:20:00 16:30:00 9.1666667
    07:20:00 16:30:00 9.1666667
    07:20:00 16:30:00 9.1666667
    06:50:00 14:00:00 7.1666667

    I want to get the result as follows:
    2.55 instead of 2.9166667
    9.55 instead of 9.8333333
    9.1 instead of 9.1666667

    I want to deduct the end_time from start_time to get in hours and minutes.
    for example 16:55 - 14.00 to be 2.55 and not 2.9166667 & so on.

    Please help. Thanks in advance.

    Regards,
    K. Mack

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by kmack View Post
    I want to get the result as follows:
    2.55 instead of 2.9166667
    9.55 instead of 9.8333333
    9.1 instead of 9.1666667
    As this "problem" is pure mathematical one, you should get acquainted with basic school arithmetic, which deals with these transformations.
    Do you really want to represent hours and minutes in one decimal figure? Are you aware that you would have to re-define arithmetical operations on it as well?
    Anyway, for just displaying purpose, you may compute hours and minutes separately. It is as easy as:
    hours = TRUNC( (END_TIME-START_TIME) * 24 ) -- only its integer part
    minutes = MOD ( (END_TIME-START_TIME) * 24, 60 ) -- remainder after division by 60
    You might need to ROUND minutes to some reasonable base if unwanted fraction will occur in the result figure.

  5. #5
    Join Date
    Jun 2011
    Posts
    11

    how to subtract date in oracle to get in hours and min.

    Hi. flyboy,

    Thanks for your help.

    Yes I want to represent the result in hours & minutes in one decimal figure.

    Regards.
    Mack

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by kmack View Post
    Yes I want to represent the result in hours & minutes in one decimal figure.
    Good luck with messing with fractions in base 60 then.
    Also enjoy any representation of seconds you choose.

  7. #7
    Join Date
    Jun 2011
    Posts
    11

    how to deduct 1 date from another to get hour and mnute

    Hi...
    Thanks for your reply...

    Please let me know how to represent the result in 1 decimal figure.

    REgards,
    MACK

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by kmack
    I want to get the result in one decimal figure in hours n minutes. I was able to do it separetly in hours and minutes..
    What about using basic add/subtract/multiply/divide features of arithmetic:
    number of hours + ( number of minutes / 100 )
    ?
    Will you ask here for getting every operation with these messed figures? You may have luck at anybody except me.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I googled that for your.

    These are one of the first hits:

    http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129
    http://orafaq.com/wiki/SQL_FAQ#How_does_one_get_the_time_difference_betwe en_two_date_columns.3F

  10. #10
    Join Date
    Sep 2011
    Posts
    9
    select ceil((To_date('2008-05-02 16:55:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-05-02 14:00:00' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60) timespan FROM DUAL;

    we can get the result 10500s, but if i divide it by 3600 , 2.91666666666667 apears again...
    Last edited by liyang900822; 09-19-11 at 04:00.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >we can get the result 10500s, but if i divide it by 3600 , 2.91666666666667 apears again...
    What result would you like instead?
    What prevents you from obtaining an acceptable answer?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Sep 2011
    Posts
    9
    2.55 instead of 2.91 as the list above ...because te timediff is 2.55 between 16:55 and 14.00

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >2.55 instead of 2.91 as the list above ...because te timediff is 2.55 between 16:55 and 14.00
    2.91 is hours.
    If YOU desire minutes, then convert .91 hours to minutes as shown below.
    Code:
    SQL> select round(.91*60) from dual;
    
    ROUND(.91*60)
    -------------
    	   55
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Sep 2011
    Posts
    9
    yeah ,it seems like it works... and i will try it tomorrow moring. u c, my time now is 1:55....too late and work tomorrow...thx very much...u offer me a lot help...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •