Home » SQL & PL/SQL » SQL & PL/SQL » Coverting time to number and subtracting the two (Oracle 9i, Windows)
Coverting time to number and subtracting the two [message #432496] Tue, 24 November 2009 09:30 Go to next message
namb
Messages: 35
Registered: September 2009
Member
Dear Friends,

I have two columns in the table in_time and out_time both are varchar type in which time is stored in 24 hour format. I want to subtract the in_time from out_time and for that i am using the following code


to_number(Trim(replace(out_time,':','.')))- 
to_number(Trim(replace(in_time,':','.')))


Now the code works fine normally but the problem is that it takes into consideration 100 not the 60 factor while subtracting as in an hour we have 60 minutes.

For an eg if the in time is 09:20 and the out_time is 19:15 the difference comes out to be 9:95 where as it should be 09:55 please help here. If the fields would have been in date type datatype the conversion is easy but how to go about in char type datatype how to find the time difference here.

Thanks in advance
Re: Coverting time to number and subtracting the two [message #432497 is a reply to message #432496] Tue, 24 November 2009 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>how to go about in char type datatype how to find the time difference here.
convert to DATE using TO_DATE() function
Re: Coverting time to number and subtracting the two [message #432498 is a reply to message #432496] Tue, 24 November 2009 09:38 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since you know how to do it with dates why don't you just to_date them to do the calculation.

Really though you should change the datatype of these columns to date, otherwise sooner or later you will get non valid times stored in them.
Previous Topic: Any function of oracle to return the quarter of sysdate
Next Topic: compare
Goto Forum:
  


Current Time: Fri Dec 02 18:17:53 CST 2016

Total time taken to generate the page: 0.14693 seconds