Home » SQL & PL/SQL » SQL & PL/SQL » how to fined total hour between a date
how to fined total hour between a date [message #342624] Sat, 23 August 2008 05:59 Go to next message
ziauldba
Messages: 55
Registered: January 2007
Location: Bangladesh
Member

Dear friend ,
how to find total hour between two dates
example...
21-aug-2008 8:15:10 and 21-aug-2008 18:25:25
my required result is...
10.10 hour..
how can i write the query........
Re: how to fined total hour between a date [message #342633 is a reply to message #342624] Sat, 23 August 2008 07:23 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
select (to_date('21-aug-2008 18:25:25','DD-MON-YYYY hh24:mi:ss') 
  -to_date('21-aug-2008 8:15:10','DD-MON-YYYY hh24:mi:ss'))*24
from dual


Regards,
Dwarak
Re: how to fined total hour between a date [message #342635 is a reply to message #342624] Sat, 23 August 2008 07:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ziauldba wrote on Sat, 23 August 2008 12:59
Dear friend ,
how to find total hour between two dates
example...
21-aug-2008 8:15:10 and 21-aug-2008 18:25:25
my required result is...
10.10 hour..
how can i write the query........

10 hours and 10 minutes is NOT written as 10.10!
You should have explained this better, so the first replier would have seen this.
Use MOD and multply by 60 to get the number of minutes after you got the number of hours.
Re: how to fined total hour between a date [message #342648 is a reply to message #342624] Sat, 23 August 2008 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select numtodsinterval(to_date('21-aug-2008 18:25:25','DD-MON-YYYY hh24:mi:ss')
  2                         - to_date('21-aug-2008 8:15:10','DD-MON-YYYY hh24:mi:ss'),
  3                         'DAY') diff
  4  from dual
  5  /
DIFF
----------------------------------
+000000000 10:10:14.999999999

1 row selected.

Regards
Michel
Re: how to fined total hour between a date [message #342673 is a reply to message #342624] Sat, 23 August 2008 22:57 Go to previous messageGo to next message
ziauldba
Messages: 55
Registered: January 2007
Location: Bangladesh
Member

Thanks..a lot my friends..
I got my result..............
Re: how to fined total hour between a date [message #342679 is a reply to message #342673] Sun, 24 August 2008 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So please post what you did.

Regards
Michel
Re: how to fined total hour between a date [message #342691 is a reply to message #342624] Sun, 24 August 2008 04:09 Go to previous messageGo to next message
ziauldba
Messages: 55
Registered: January 2007
Location: Bangladesh
Member

Dear Friend Michel Cadot...
i did it like that..


select (TO_DATE( to_char(sysdate ,'dd-mm-yy')|| ',' || '17:40:10' ,'dd-mm-yy hh24:mi:ss')
- TO_DATE( to_char(sysdate ,'dd-mm-yy')|| ',' || '17:20:10' ,'dd-mm-yy hh24:mi:ss'))*24*60 as "min"
Re: how to fined total hour between a date [message #342695 is a reply to message #342691] Sun, 24 August 2008 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Correct by this does not fit your requirement:
Quote:
my required result is...
10.10 hour..

Regards
Michel
Re: how to fined total hour between a date [message #342709 is a reply to message #342691] Sun, 24 August 2008 09:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ziauldba wrote on Sun, 24 August 2008 11:09
Dear Friend Michel Cadot...
i did it like that..


select (TO_DATE( to_char(sysdate ,'dd-mm-yy')|| ',' || '17:40:10' ,'dd-mm-yy hh24:mi:ss')
- TO_DATE( to_char(sysdate ,'dd-mm-yy')|| ',' || '17:20:10' ,'dd-mm-yy hh24:mi:ss'))*24*60 as "min"

Your concatenated string containing date and time does not match the format mask to convert it back to a date. (The comma you use as separator)
Re: how to fined total hour between a date [message #343447 is a reply to message #342709] Wed, 27 August 2008 05:34 Go to previous message
ziauldba
Messages: 55
Registered: January 2007
Location: Bangladesh
Member

Frank wrote on Sun, 24 August 2008 20:07
ziauldba wrote on Sun, 24 August 2008 11:09
Dear Friend Michel Cadot...
i did it like that..


select (TO_DATE( to_char(sysdate ,'dd-mm-yy')|| ',' || '17:40:10' ,'dd-mm-yy hh24:mi:ss')
- TO_DATE( to_char(sysdate ,'dd-mm-yy')|| ',' || '17:20:10' ,'dd-mm-yy hh24:mi:ss'))*24*60 as "min"

Your concatenated string containing date and time does not match the format mask to convert it back to a date. (The comma you use as separator)

Pls Give the proper Answer.......
Previous Topic: procedures using cursors
Next Topic: Can a variable be set as Default null which reference a not null column?
Goto Forum:
  


Current Time: Sat Dec 03 11:43:52 CST 2016

Total time taken to generate the page: 0.07971 seconds