Home » SQL & PL/SQL » SQL & PL/SQL » Extracting Days,Hours between 2 dates (Oracle 9i , Win xp)
Extracting Days,Hours between 2 dates [message #361433] Wed, 26 November 2008 07:01 Go to next message
adilsami
Messages: 46
Registered: October 2007
Member
Hi there,

am facing problem in extracting Days,Hours,Minutes between 2 dates
e.g ( sysdate - datefield)

here's what ive tried so far , am getting some of the results in negative .

select sysdate,workorder.statusdate,
(extract(day from sysdate))-(extract(day from workorder.statusdate))Days,
to_char(sysdate,'HH24') - to_char(workorder.statusdate,'HH24')Hours, 
to_char(sysdate,'MI') - to_char(workorder.statusdate,'MI')Minutes
from workorder;


The Result:
SYSDATE	                 STATUSDATE	       DAY-HOUR-MINUTES

11/26/2008 3:54:41 PM	11/11/2008 7:41:04 PM	(15)(-4)(13)
11/26/2008 3:54:41 PM	11/11/2008 5:06:23 PM	(15)(-2)(48)
11/26/2008 3:54:41 PM	11/11/2008 5:06:23 PM	(15)(-2)(48)
11/26/2008 3:54:41 PM	11/16/2008 10:51:26 AM	(10) (5	(3)
11/26/2008 3:54:41 PM	11/16/2008 5:47:11 PM	(10)(-2)(7)

Re: Extracting Days,Hours between 2 dates [message #361435 is a reply to message #361433] Wed, 26 November 2008 07:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
try usein Timestamps - you get the results as intervals:
select to_timestamp(sysdate) - to_timestamp(sysdate-3.5) from dual;


Or, for a more formated result:
select extract( day from to_timestamp(sysdate) - to_timestamp(sysdate-3.5)) day
      ,extract( hour from to_timestamp(sysdate) - to_timestamp(sysdate-3.5)) hour
      ,extract( minute from to_timestamp(sysdate) - to_timestamp(sysdate-3.5)) minute
      ,extract( second from to_timestamp(sysdate) - to_timestamp(sysdate-3.5)) second
from dual;
Re: Extracting Days,Hours between 2 dates [message #361436 is a reply to message #361433] Wed, 26 November 2008 07:16 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
If you would have searched you would have had an answer by this time. Check this link.

http://asktom.oracle.com/tkyte/Misc/DateDiff.html

Regards

Raj
Previous Topic: creating database links for other user
Next Topic: Oracle StoredProcedure returning Object instead of Cursor
Goto Forum:
  


Current Time: Fri Dec 09 09:31:01 CST 2016

Total time taken to generate the page: 0.05189 seconds