Re: Date Difference

From: Ajay M Gandhi <agandhi_at_osf1.gmu.edu>
Date: 16 Jan 1995 17:36:28 GMT
Message-ID: <3feaqs$d47_at_portal.gmu.edu>

Ajay M Gandhi (agandhi_at_osf1.gmu.edu) wrote:
: Vince Cross (bartok_at_bnr.ca) wrote:
: : In article <3f42cq$3un_at_portal.gmu.edu>,
: : Ajay M Gandhi <agandhi_at_mason1.gmu.edu> wrote:
: : >Hi,
: : >
: : >Is there any function that can find the number of hours and
: : >minutes between two date functions?
: : >
: : >For example, I am interested in getting 02:30 with the follwoing
: : >data.
: : >
: : >Start date = 12-Jan-1995
: : >End Date = 12-Jan-1995.
: : >
: : >Both the above dates were updated into the database at an
: : >interval of 2 hrs and 30 mins.
: : >

: : This might work for you:

: : select TO_NUMBER(TO_CHAR(end_date, 'HH24'))-TO_NUMBER(TO_CHAR(start_date,
: : 'HH24')) || ':' || TO_NUMBER(TO_CHAR(end_date, 'MI'))-TO_NUMBER(
: : TO_CHAR(start_date, 'MI'))
: : from table_name;
: -- What happens if the start hour is 23:00 and end hour is 01:00 (during
: midnight). The difference will be 1 - 23 = -22 and which I don't want. The
: same concepts will be true for minutes and seconds.

: -- If I want to find the duration between two dates which are six months apart
: then again the above solution is not going to work. Am I right ?

: -- Anyway Thanks for the hint. I think I have the solution, I will post it in a few minutes.

: Thanks

: Ajay Gandhi
: *******************************************************************
: SIRC => Software Information Resource Corp. - Meeting Future Needs
: *******************************************************************

: : Enjoy,
: : Vince
: : --
: : * disclaimer - My views respresent NT/BNR in every way, NOT!
: : * Please direct non-business email to VLCross_at_aol.com (No, I'm not an idiot
: : just because I have an aol account. Excessive drinking makes me one.)
: : * Work related stuff can go to bartok_at_bnr.ca

The solution which I came up was to find the total seconds and then find the hours and minutes

/* calculate the total seconds difference */

select ((to_number(to_char(end_time, 'SSSSS'))+ (trunc(end_time) - trunc(start_time)) * 86400) - to_number(to_char(start_time,'SSSSS')))
into sec_diff from dual;

/* Calculate the number of hours which will be a number */

select trunc(sec_diff/3600) into nhours from dual;

select lpad(rtrim(ltrim(to_chars(nhours))),2,0) into hours from dual;

select mod(to_number(sec_diff),3600)/60 into nmins from dual;

select lpad(rtrim(ltrim(to_char(trunc(nmins)))),2,0) into mins from dual;

select mod(to_number(sec_diff),3600)/60 into nsecs from dual;

select round((nsecs - trunc(nsecs))*60) into nsecs from dual;

select lpad(rtrim(ltrim(to_char(nsecs)))),2,0) into secs froim

select rtrim(hours)||':'||rtrim(mins)||':'||rtrim(secs) into duration from dual;

: Is there any function that can find the number of hours and
: minutes between two date functions?

: For example, I am interested in getting 02:30 with the follwoing
: data.

: Start date = 12-Jan-1995
: End Date = 12-Jan-1995.