OraFAQ Forum: SQL & PL/SQL » Difference in Minutes

Home » SQL & PL/SQL » SQL & PL/SQL » Difference in Minutes

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

Difference in Minutes [message #10984]

Fri, 27 February 2004 04:10

Nirene
Messages: 22
Registered: February 2004

Junior Member

select ro_num,to_char(endtime,'dd/mm/yy hh24:mm:ss'),to_char(starttime,'dd/mm/yy hh24:mm:ss'),floor((endtime-starttime)*24*60) as diff from timedb where ro_num='ROAB309642OA'

I've a table with Starttime,Endtime & Ro_num in a Table TimeDB.I want to find the difference in minutes getting the 2 given dates.When I execute this query I get the result as

RO_NUM TO_CHAR(ENDTIME,' TO_CHAR(STARTTIME DIFF
------------ ----------------- ----------------- ----------
ROAB309642OA 22/01/04 10:01:35 22/01/04 09:01:00 51

ROAB309642OA 22/01/04 17:01:41 22/01/04 16:01:12 70

ROAB309642OA 22/01/04 18:01:23 22/01/04 18:01:15 0

Here the 1st record difference should be 60 minutes instead on 51

2nd should 60 instead of 70

and the 3rd as 0 which is correct

Give a query to solve this problem.

Nirene

Report message to a moderator

Re: Difference in Minutes [message #10987 is a reply to message #10984]

Fri, 27 February 2004 06:31

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Nirene,

Months are MM, minutes are MI:

SQL> SELECT TO_CHAR(td.endtime
  2         ,       'DD/MM/YYYY HH24:<FONT COLOR=RED><B>MI</B></FONT>:SS')     endtime
  3  ,      TO_CHAR(td.starttime
  4         ,       'DD/MM/YYYY HH24:<FONT COLOR=RED><B>MI</B></FONT>:SS')     starttime
  5  ,      FLOOR((endtime - starttime)*24*60)   diff_in_min
  6  FROM   timedb           td
  7  /
 
ENDTIME             STARTTIME           DIFF_IN_MIN
------------------- ------------------- -----------
22/01/2004 10:01:35 22/01/2004 09:01:00          60
22/01/2004 17:01:41 22/01/2004 16:01:12          60
22/01/2004 18:01:23 22/01/2004 18:01:15           0
 
SQL>

Art.

Report message to a moderator

Previous Topic:	Remote Dependencise mode problem
Next Topic:	generating sequence

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 18:34:18 CDT 2024