Re: Date Difference

From: Ajay M Gandhi <agandhi_at_osf1.gmu.edu>
Date: 25 Jan 1995 16:24:29 GMT
Message-ID: <3g5tvt$q2c_at_portal.gmu.edu>

Tommy Wareing (p0070621_at_brookes.ac.uk) wrote:
: In article <3feaqs$d47_at_portal.gmu.edu>, agandhi_at_osf1.gmu.edu (Ajay M Gandhi) says:
: >Ajay M Gandhi (agandhi_at_osf1.gmu.edu) wrote:
: >: Vince Cross (bartok_at_bnr.ca) wrote:
: >: : Ajay M Gandhi <agandhi_at_mason1.gmu.edu> wrote:
: >: : >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.
: >: -- 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 ?
 

: My turn!
 

: TRUNC(end-start) days,
: TO_DATE(end-start+TRUNC(SYSDATE), 'HH24:MI') hours

Now it's my turn !

For example, "End-date = '12-JAN-1995-02:30:00'

            Start_date = '01-JAN-1995-02:30:00'

Now we apply the above trunc(end-start) then the days returned are "10" instead of "11".

So you may have to do trunc(end) - trunc(start) to get "11" days.

Also I tried the other part of the select statement, it doesn't work in sqlplus. The reason being that when you subtract END_date - start_date
, it gives result in decimals and then we are trying to convert it into date format, which "to_date" function doesn't like.

: (if you really want the number of seconds difference, then
: use (end-start)*24*60*60 instead of that *REALLY* horrible expression)

I agree that was a horrible expression, but I had no other option at that moment.

Thanks for responding.

Ajay Gandhi
SIRC
Fax :(703)-803-8930
e-mail : agandhi_at_gmu.edu

---

SIRC => Software Information Resource Corp. - Meeting Future Needs

---

Received on Wed Jan 25 1995 - 17:24:29 CET