Home » SQL & PL/SQL » SQL & PL/SQL » How to get correct minute between two hours? (11g)
How to get correct minute between two hours? [message #569172] Mon, 22 October 2012 01:47 Go to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi all,

Hope doing well,

sir i am using one query but not getting correct minutes.

here is my query:

v_Interval:= to_timestamp(v_temphrs,'HH24:MI:SS')-to_timestamp(v_outpunch1,'HH24:MI:SS');
v_TotalHrsMin1 := extract(hour from v_interval) * 60 + extract(minute from v_interval);


here v_interval datatype is "interval day to second" and v_temphrs datatype is varchar2 and value is : 12:00:00 and v_outpunch1 datatype is varchar2 and value is: 06:10:00
and v_totalHrsMin1 datatype is number.

here i should get value 370.
but i am getting value 350.

could u check that why it's happening?

thanks,
Re: How to get correct minute between two hours? [message #569173 is a reply to message #569172] Mon, 22 October 2012 01:57 Go to previous messageGo to next message
_jum
Messages: 485
Registered: February 2008
Senior Member
Get the same (correct) result:
SELECT extract(hour   from to_timestamp('12:00:00','HH24:MI:SS')-to_timestamp('06:10:00','HH24:MI:SS')) * 60 + 
       extract(minute from to_timestamp('12:00:00','HH24:MI:SS')-to_timestamp('06:10:00','HH24:MI:SS')) v_totalHrsMin1 
  FROM dual;

V_TOTALHRSMIN1
--------------
           350
1 row selected.

Between these timestamps, there are 5 hours and 50 minutes = 350 minutes.

[Updated on: Mon, 22 October 2012 01:58]

Report message to a moderator

Re: How to get correct minute between two hours? [message #569174 is a reply to message #569172] Mon, 22 October 2012 02:02 Go to previous messageGo to next message
flyboy
Messages: 1750
Registered: November 2006
Senior Member
vikashkrburnwal@gmail.com wrote on Mon, 22 October 2012 08:47
could u check that why it's happening?

Oracle returns what you requested it to - difference in hours between two today's times given in 24 hour format.
12:00:00 hours = 720 minutes from midnight
06:10:00 hours = 370 minutes from midnight
The difference between them is 350 minutes, which is correct. If you expect something else, you should exactly define rules for achieving it.
Re: How to get correct minute between two hours? [message #569180 is a reply to message #569173] Mon, 22 October 2012 03:59 Go to previous message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
thanks sir i got it.
Previous Topic: How to bypass putting select inside a count function in the following query ?
Next Topic: Oracle Group By Error!!! Please help
Goto Forum:
  


Current Time: Sun Apr 20 00:42:26 CDT 2014

Total time taken to generate the page: 0.03728 seconds