Difference between Dates
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Difference between Dates

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    Hi,
    I need to find the exact number of days, hours ,minutes and seconds elapsed between two given dates (Given format is in 'ddmmyyhhmiss' format)
    Please can somebody advice me
    Badrinath

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I'm writing this without having the database anywhere near, so there might by some errors or typos.... BTW, I hope your given date format has HH24 instead of HH, or you have to have AM indicator somewhere in your date.

    SELECT
    TRUNC(TO_DATE(date1,''ddmmyyhh24miss') - TO_DATE(date2,''ddmmyyhh24miss')) days,
    TRUNC((TO_DATE(date1,''ddmmyyhh24miss') - TO_DATE(date2,''ddmmyyhh24miss'))*24) hours,
    TRUNC((TO_DATE(date1,''ddmmyyhh24miss') - TO_DATE(date2,''ddmmyyhh24miss'))*24*60) mins,
    TRUNC((TO_DATE(date1,''ddmmyyhh24miss') - TO_DATE(date2,''ddmmyyhh24miss'))*24*60*60) secs
    FROM dual;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jan 2000
    Location
    Derby, Derbyshire, UK
    Posts
    3
    The previous solution gives the number of days or hours or minutes or seconds between your two dates (e.g. if the dates are 1 hour apart, the seconds value will be 3600). If you want the difference between two dates presented as
    "days-hours:minutes:seconds", try the SQL below. Note that this uses two DATE columns, d1 and d2. In your case, you will need to substitute TO_DATE(date1,'ddmmyyhh24miss') for d1 and TO_DATE(date1,'ddmmyyyyhh24miss') for d2.

    select to_char( floor( d2 - d1 ) ) || '-' || to_char( to_date( to_char( floor( ( ( d2 - d1 ) - floor( d2 - d1 ) ) * 86400 ), '00000' ), 'sssss' ), 'hh24:mi:ss' ) from junk;

    This is based on the fact that the difference between two DATEs returns a fractional number of days and that there are 86400 seconds in a day. So, floor(d2-d2) returns the integer number of days between the two dates, and (d2-d2)-floor(d2-d1) returns the remaining fraction of a day between the two dates. Multiplying this by 86400 gives the number of seconds. This is truncated to integer (using floor(...)) before passing it into TO_CHAR so that we don't end up with any decimal places. The resulting 5-digit number is a valid time format and so can be converted to a date using the 'sssss' format. The resulting date can be converted to HH:MM:SS using the HH24:MI:SS date picture.

Posting Permissions

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



Click Here to Expand Forum to Full Width