Home » RDBMS Server » Server Administration » Difference between dates in hours and minutes
Difference between dates in hours and minutes [message #370274] Thu, 22 July 1999 16:16 Go to next message
marcus
Messages: 9
Registered: July 1999
Junior Member
Dear friends

I am trying to make a query where one column would be the difference between the end and the start of a request process (in this case a request process takes some hours). The fields are START_DT and END_DT and I am not able to format the difference in hours.
Does somebody can help me?
Re: Difference between dates in hours and minutes [message #370276 is a reply to message #370274] Thu, 22 July 1999 20:43 Go to previous messageGo to next message
CT
Messages: 3
Registered: July 1999
Junior Member
try this example.

select to_char(to_date(to_char(to_number(to_char(adate, 'sssss')) -
to_number(to_char(bdate,'sssss')) ,'99999999'),'sssss'), 'hh24')

I hope this helps.
from clcomp;
Re: Difference between dates in hours and minutes [message #370277 is a reply to message #370276] Thu, 22 July 1999 20:45 Go to previous messageGo to next message
CT
Messages: 3
Registered: July 1999
Junior Member
opps.. typo
select to_char(to_date(to_char(to_number(to_char(adate, 'sssss')) -
to_number(to_char(bdate,'sssss')) ,'99999999'),'sssss'), 'hh24:mi:ss')
from clcomp;

hope this helps.
Re: Difference between dates in hours and minutes [message #370281 is a reply to message #370274] Mon, 26 July 1999 07:06 Go to previous message
Chris Hunt
Messages: 27
Registered: March 1999
Junior Member
The difference between two date fields is expressed in days (and fractions of days). For example I've just run this...
SQL> select sysdate - to_date('26-JUL-99') from dual
SQL> /

SYSDATE-TO_DATE('26-JUL-99')
----------------------------
                   .49736111

So to convert this figure into hours, just multiply it by 24. Here's a query term returning time in hours (to one deciml place)...
ROUND((end_dt - start_dt) * 24,1) hours

To get hours and minutes columns, I use TRUNC and MOD...
SELECT TRUNC((end_dt - start_dt) * 24) hours,
       MOD((end_dt - start_dt) * 1440,60) mins
FROM   my_table...

Hope this helps

Chris Hunt

Previous Topic: Encrypt/decrypt a column dbms_crypto package
Next Topic: How to read from file in PL/SQL
Goto Forum:
  


Current Time: Fri Oct 23 16:46:18 CDT 2020