Home » SQL & PL/SQL » SQL & PL/SQL » Date/Hours Calculation
Date/Hours Calculation [message #39418] Tue, 16 July 2002 07:09 Go to next message
Huw Thomas
Messages: 1
Registered: July 2002
Junior Member
I am trying to calculate hours difference between 2 dates, formatted dd-mon-rrrr hh24:mi, but based on a working day. The working day is between 08:30 hours and 17:00 hours.

Obviously it is straightforward if the two dates fall on the same day.
Re: Date/Hours Calculation [message #39426 is a reply to message #39418] Tue, 16 July 2002 10:08 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
sql>select date1, date2, 
  2         ((date2 - date1) * 24) - 
  3            decode(trunc(date2) - trunc(date1), 
  4              0, 0, 
  5              (trunc(date2) - trunc(date1)) * 15.5) elapsed_hours
  6    from t2
  7   order by date1;  
 
DATE1               DATE2               ELAPSED_HOURS
------------------- ------------------- -------------
07/16/2002 10:22:00 07/16/2002 16:37:00          6.25
07/16/2002 14:34:00 07/17/2002 10:17:00     4.2166667
07/17/2002 09:37:00 07/17/2002 10:37:00             1
07/18/2002 16:19:00 07/20/2002 16:19:00            17
07/18/2002 16:30:00 07/18/2002 17:00:00            .5
07/18/2002 17:00:00 07/19/2002 09:00:00            .5
07/19/2002 17:00:00 07/20/2002 17:00:00           8.5
Previous Topic: Substitue for TRUNCATE
Next Topic: Adding Counter inside a select statement
Goto Forum:
  


Current Time: Thu Mar 28 05:06:37 CDT 2024