| Calulate the Eaxct Hours, Minutes second [message #229034] |
Thu, 05 April 2007 02:13  |
shahzad-ul-hasan
Messages: 643 Registered: August 2002
|
Senior Member |
|
|
Oracle Verson: 8.0.5 For NT/2000 Enterprize Edition
Table Structure:
Field: DataType: Input Value:
Empcode Number(8)
Intime: Varchar2(40)
22-Mar-2007 10:22:12
22-Mar-2007 14:32:22
Otime: Varchar2(40)
22-Mar-2007 13:01:42
22-Mar-2007 16:32:22
Please advised me, I want to calculate the exact hours:min:ss from the whole day which he has perform several type in/out.
Please send me a fuction, or procedure for database level handling, because I want to use it in Reports.
Regards.
Shahzad
|
|
|
|
| Re: Calulate the Eaxct Hours, Minutes second [message #229035 is a reply to message #229034] |
Thu, 05 April 2007 02:31   |
 |
Michel Cadot
Messages: 68774 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
You can get the number of seconds with:
SQL> select (to_date('22-Mar-2007 13:01:42','DD-Mon-YYYY HH24:MI:SS')
2 -to_date('22-Mar-2007 10:22:12','DD-Mon-YYYY HH24:MI:SS'))*86400 seconds
3 from dual;
SECONDS
----------
9570
1 row selected.
Using trunc and mod it is easy to change it in hours/minutes/seconds:
SQL> with
2 data as (
3 select (to_date('22-Mar-2007 13:01:42','DD-Mon-YYYY HH24:MI:SS')
4 -to_date('22-Mar-2007 10:22:12','DD-Mon-YYYY HH24:MI:SS'))*86400 seconds
5 from dual
6 )
7 select trunc(seconds/3600) hours,
8 trunc(mod(seconds,3600)/60) minutes,
9 mod(seconds,60) seconds
10 from data
11 /
HOURS MINUTES SECONDS
---------- ---------- ----------
2 39 30
1 row selected.
Regards
Michel
|
|
|
|
| Re: Calulate the Eaxct Hours, Minutes second [message #431960 is a reply to message #229035] |
Fri, 20 November 2009 04:20   |
shahzad-ul-hasan
Messages: 643 Registered: August 2002
|
Senior Member |
|
|
with
data as (
select (to_date('22-Mar-2007 13:01:42','DD-Mon-YYYY HH24:MI:SS')
-to_date('22-Mar-2007 10:22:12','DD-Mon-YYYY HH24:MI:SS'))*86400 seconds
from dual
)
select trunc(seconds/3600) hours,
trunc(mod(seconds,3600)/60) minutes,
mod(seconds,60) seconds
from data
/
i am using oracle 8.0.5 and form 6. pls advised will it work?
|
|
|
|
| Re: Calulate the Eaxct Hours, Minutes second [message #431963 is a reply to message #431960] |
Fri, 20 November 2009 04:24  |
 |
Michel Cadot
Messages: 68774 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Put "data" subquery inside "from".
Regards
Michel
|
|
|
|