OraFAQ Forum: Server Administration » Difference between dates in hours and minutes

Home » RDBMS Server » Server Administration » Difference between dates in hours and minutes

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference between dates in hours and minutes [message #370274]

Thu, 22 July 1999 16:16

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?

Report message to a moderator

Re: Difference between dates in hours and minutes [message #370276 is a reply to message #370274]

Thu, 22 July 1999 20:43

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;

Report message to a moderator

Re: Difference between dates in hours and minutes [message #370277 is a reply to message #370276]

Thu, 22 July 1999 20:45

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.

Report message to a moderator

Re: Difference between dates in hours and minutes [message #370281 is a reply to message #370274]

Mon, 26 July 1999 07:06

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

Extra Connections Ltd - my consultancy company

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 11 10:33:38 CDT 2025