| Find difference interm of HH24:MM:SS [message #481788] |
Sat, 06 November 2010 11:01  |
shahzad-ul-hasan
Messages: 615
Registered: August 2002
|
Senior Member |
|
|
i want to calculate the diffrence between the intime and outtime.pls see the table structure for further understanding.
Id intime: Outime: Date
10001 2-Nov-2010 12:23:00 2-Nov-2010 04:23:22 2-Nov-2010
-
Attachment: 1233.JPG
(Size: 8.42KB, Downloaded 936 times)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Find difference interm of HH24:MM:SS [message #481837 is a reply to message #481828] |
Sun, 07 November 2010 09:40  |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
It seems that you'll have to do that a little bit harder.
If you use TO_DATE, then difference between two (date) values is a number of days:SQL> select to_date('2-Nov-2010 12:23:00','DD-MON-YYYY HH24:MI:SS')
2 -
3 to_date('2-Nov-2010 04:23:22','DD-MON-YYYY HH24:MI:SS') res
4 from dual;
RES
----------
.333078704
Now you have to create hours, minutes and seconds - based on the previous result.
Number of hours: as day has 24 hours, simply multiply the previous result with 24:SQL> select 0.333078704 * 24 hours from dual;
HOURS
----------
7.9938889SQL> select 0.9938889 * 60 minutes from dual;
MINUTES
----------
59.633334 SQL> select 0.633334 * 60 seconds from dual;
SECONDS
----------
38.00004
Finally, the result is the same as Michel's - you'll just need to compose it.
[EDIT: fixed " * 24" query - was "days" instead of "hours"]
[Updated on: Sun, 07 November 2010 23:59] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
Sorry, my bad, I'll fix it right now.