Click here to Skip to main content
11,637,415 members (64,344 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Oracle
Below is my Query :
Quote:
Select a.Sent, b.Recd, ((b.Recd-a.Sent)*1440) DIFF
from TableA b, TableB a
where b.type = 'AAA'
and b.ID = a.ID1


Where ID and ID1 are foreign Key
Both sent, Recd dates are in format like '
Quote:
5/9/2011 5:22:00 PM
'

But i am not getting :
Quote:
((b.Recd-a.Sent)*1440)
in minutes
Instead i am getting like
Quote:
-000000000 06:48:00



I need the difference of DATE in Minutes only.

Please suggest.
Posted 4-Feb-13 5:43am
Nibin22644
Edited 4-Feb-13 5:44am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

SELECT
trunc(DATE1-DATE2) days,
mod( trunc( ( DATE1-DATE2 ) * 24 ), 24) HOURS,
mod( trunc( ( DATE1-DATE2 ) * 1440 ), 60 ) MINUTES,
mod( trunc( ( DATE1-DATE2 ) * 86400 ), 60 ) SECONDS
FROM dual;
change as per ur table

check out this link
http://stackoverflow.com/questions/206222/oracle-best-select-statement-for-getting-the-difference-in-minutes-between-two[^]
  Permalink  
Comments
Nibin22 at 4-Feb-13 23:39pm
   
It is Working !!! Thanks :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

This should do it:
select
  round((second_date - first_date) * 1440,2)
You need to round the value.
  Permalink  
Comments
Nibin22 at 4-Feb-13 23:39pm
   
It is Working !!! Thanks :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

I suggest that you try using the NUMTODSINTERVAL[^] function with the second parameter of 'MINUTE'.

 NUMTODSINTERVAL(b.recd - a.sent, 'MINUTE')
  Permalink  
Comments
Nibin22 at 4-Feb-13 23:39pm
   
It is Working !!! Thanks :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Thanks Friends,

I figured like below :

Select a.Sent, b.Recd, (Extract Minute from b.Recd-a.Sent) DIFF
  from TableA a, TableB b
 where b.type = 'AAA'
   and b.ID = a.ID1;


Where " (Extract Minute from b.Recd-a.Sent) DIFF" returns the date difference in MINUTES
  Permalink  
Comments
Nibin22 at 4-Feb-13 23:38pm
   
Nice!!! I didnt imagine this number of solutions will be there to find the date difference!!!

Thanks a lot Guys !!!! :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 314
1 OriginalGriff 295
2 stibee 288
3 jyo.net 270
4 himanshu agarwal 209
0 OriginalGriff 9,031
1 Sergey Alexandrovich Kryukov 8,643
2 Mika Wendelius 6,959
3 F-ES Sitecore 2,388
4 Suvendu Shekhar Giri 2,320


Advertise | Privacy | Mobile
Web03 | 2.8.150728.1 | Last Updated 4 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100