Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Subtracting Oracle dates/times

    I have two columns that contain dates. When I subtract them, in most cases I get the correct difference in time (the time part is all I care about), but in some cases I don't get the correct results.

    For example:

    3/31/2001 5:00:00 PM - 3/31/2001 3:20:00 PM

    yields: 0 Days 1 Hrs 39 Min 59 Sec

    I was expecting 0 Days 1 Hrs 40 Min 0 Sec

    Code:
    SELECT loc
         , descr
         , p_difexpectedtime
         , p_difoverduetime
         , trunc((((86400*(p_difoverduetime-p_difexpectedtime))/60)/60)/24) "Days"
         , trunc(((86400*(p_difoverduetime-p_difexpectedtime))/60)/60)-24*(trunc((((86400*(p_difoverduetime-p_difexpectedtime))/60)/60)/24)) "Hrs"
         , trunc((86400*(p_difoverduetime-p_difexpectedtime))/60)-60*(trunc(((86400*(p_difoverduetime-p_difexpectedtime))/60)/60)) "Min"
         , trunc(86400*(p_difoverduetime-p_difexpectedtime))-60*(trunc((86400*(p_difoverduetime-p_difexpectedtime))/60)) "Sec"
    FROM loc

  2. #2
    Join Date
    Jul 2004
    Posts
    6
    select trunc(mydate / 3600) myhour,
    trunc(mod(mydate, 3600) / 60) myminute,
    mod(mydate, 60) mysecond
    from (select round((to_date('2001-03-31 5:00:00', 'yyyy-mm-dd hh24:mi:ss') -
    to_date('2001-03-31 3:20:00', 'yyyy-mm-dd hh24:mi:ss')) *
    86400) mydate
    from dual) a

    this is my sql,it can get the correct results,you must use function "round" to fix the float operation.

Posting Permissions

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