Home » SQL & PL/SQL » SQL & PL/SQL » how to add hour and minute (Oracle 9i, Windows XP)
how to add hour and minute [message #314046] Tue, 15 April 2008 12:43 Go to next message
gnod
Messages: 19
Registered: December 2007
Junior Member
Hi,

how can i add hour and minutes?
for ex: (8 hour and 27 mins) + (7 hours and 48 mins)


Thanks..
Re: how to add hour and minute [message #314048 is a reply to message #314046] Tue, 15 April 2008 12:52 Go to previous messageGo to next message
gnod
Messages: 19
Registered: December 2007
Junior Member
thanks for your reply..
can you give me a sample select statement?


thanks..
Re: how to add hour and minute [message #314049 is a reply to message #314048] Tue, 15 April 2008 12:54 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Whoops, I deleted my response (sorry) after seeing that you did not want to add 1 hour and 1 minute to a date. I misread the question.
Re: how to add hour and minute [message #314051 is a reply to message #314046] Tue, 15 April 2008 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on what is the input data datatype.

Regards
Michel
Re: how to add hour and minute [message #314062 is a reply to message #314051] Tue, 15 April 2008 13:40 Go to previous messageGo to next message
gnod
Messages: 19
Registered: December 2007
Junior Member
when i look at the column "Elapse Time",
the data is 08:27 which is 8hrs and 27 mins..
i'll look the data type when i get back at the office..

is it possible to show a sample for some basic datatype?


thanks..
Re: how to add hour and minute [message #314065 is a reply to message #314062] Tue, 15 April 2008 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database SQL Reference
Chapter 2 Basic Elements of Oracle SQL
Section Datatypes

When you'll like at datatypes, also look at data and post a test case: create table and insert statements.
Also post the result you want with these data.

Regards
Michel
Re: how to add hour and minute [message #314140 is a reply to message #314065] Tue, 15 April 2008 20:57 Go to previous messageGo to next message
gnod
Messages: 19
Registered: December 2007
Junior Member
the datatype is varchar2..

select elapsed_time
from load
where id in ('TL11757', 'TL11818');


the result of this sql is:
28:51
08:53

i want to add 28:51 + 08:53 and the result should be 37:04


thanks,
Re: how to add hour and minute [message #314142 is a reply to message #314046] Tue, 15 April 2008 21:14 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>i want to add 28:51 + 08:53 and the result should be 37:04
Only if you failed basic mathematics.
(There are only 60 seconds in a minute; not 100.)

Let Oracle do the summation, because it will get the correct answer.

[Updated on: Tue, 15 April 2008 21:18] by Moderator

Report message to a moderator

Re: how to add hour and minute [message #314168 is a reply to message #314142] Tue, 15 April 2008 22:46 Go to previous messageGo to next message
gnod
Messages: 19
Registered: December 2007
Junior Member
if i use the SUM, it generates error ORA-01722: invalid number
select sum(elapsed_time)
from load
where id in ('TL11757', 'TL11818');
Re: how to add hour and minute [message #314169 is a reply to message #314046] Tue, 15 April 2008 22:51 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Read & follow posting guidelines as stated in URL above.

You have not posted near enough detail to replicate your error.

Yes, you are doing things wrong which is why the error.
Re: how to add hour and minute [message #314186 is a reply to message #314169] Wed, 16 April 2008 00:24 Go to previous messageGo to next message
gnod
Messages: 19
Registered: December 2007
Junior Member
to simplify my question

how do you add 28hrs and 51mins to 8hrs and 53mins? which is varchar2 datatype..

select '28:51' + '08:53'
from dual;



thanks.
Re: how to add hour and minute [message #314187 is a reply to message #314046] Wed, 16 April 2008 00:29 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
one does not "add" two strings.
what is 'ID10t' + '1'?
Re: how to add hour and minute [message #314197 is a reply to message #314187] Wed, 16 April 2008 01:01 Go to previous messageGo to next message
gnod
Messages: 19
Registered: December 2007
Junior Member
my question is
what is the necessary function to add the strings to make it 37:04
Re: how to add hour and minute [message #314201 is a reply to message #314140] Wed, 16 April 2008 01:22 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
gnod
i want to add 28:51 + 08:53 and the result should be 37:04

That's how I add minutes and seconds:
28 min + 08 min = 36 min
51 sec + 53 sec = 104 sec = 1 min 44 sec

36 min + 1 min 44 sec = 37 min 44 sec


gnod
what is the necessary function to add the strings to make it 37:04

I really wouldn't know what function makes "37:04" if it should be "37:44".
Re: how to add hour and minute [message #314204 is a reply to message #314046] Wed, 16 April 2008 01:24 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
what is 'ID10t' + '1'?
Please repeat the answer.
Re: how to add hour and minute [message #314212 is a reply to message #314201] Wed, 16 April 2008 01:36 Go to previous messageGo to next message
gnod
Messages: 19
Registered: December 2007
Junior Member
Littlefoot,

how i will add because the datatype of that column is varchar2?


thanks,
Re: how to add hour and minute [message #314216 is a reply to message #314212] Wed, 16 April 2008 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
gnod wrote on Wed, 16 April 2008 08:36
Littlefoot,

how i will add because the datatype of that column is varchar2?


thanks,

What is your opinion?
Do you think Oracle has a built-in function that sees that these strings are hours:minutes and add them?

Regards
Michel

Re: how to add hour and minute [message #314228 is a reply to message #314216] Wed, 16 April 2008 02:16 Go to previous messageGo to next message
gnod
Messages: 19
Registered: December 2007
Junior Member
i thought it can add the hrs and mins even if the datatype is varchar2..

when i look at the table, i can subtract the "TRIP_END_DATE" to "BEST_DEP"..

BEST_DEP TRIP_END_DATE (TRIP_END_DATE - BEST_DEP)
4/12/2008 8:00:00am 4/12/2008 4:29:00PM 0.353472222222222


the result should be 8:29


thanks
Re: how to add hour and minute [message #314237 is a reply to message #314228] Wed, 16 April 2008 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unit of difference is day, so...

Regards
Michel
Re: how to add hour and minute [message #314245 is a reply to message #314237] Wed, 16 April 2008 02:48 Go to previous messageGo to next message
gnod
Messages: 19
Registered: December 2007
Junior Member
how do i make it 8:29 in "(TRIP_END_DATE - BEST_DEP)" column?
Re: how to add hour and minute [message #314247 is a reply to message #314245] Wed, 16 April 2008 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1 day = 24 hours = 1440 minutes = 86400 seconds.

Regards
Michel
Re: how to add hour and minute [message #314248 is a reply to message #314237] Wed, 16 April 2008 02:55 Go to previous message
cherry
Messages: 56
Registered: December 2007
Member
Convert Seconds to Hours, Minutes, and Seconds
Previous Topic: ORA-00984: column not allowed here
Next Topic: What would be the best solution to copy/process the data?
Goto Forum:
  


Current Time: Mon Dec 05 23:57:05 CST 2016

Total time taken to generate the page: 0.10903 seconds