Home » SQL & PL/SQL » SQL & PL/SQL » Calulate the Eaxct Hours, Minutes second
Calulate the Eaxct Hours, Minutes second [message #229034] Thu, 05 April 2007 02:13 Go to next message
shahzad-ul-hasan
Messages: 454
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 454
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 Go to previous message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: ORA-01722: invalid number
Next Topic: query fetching wrong results
Goto Forum:
  


Current Time: Sat Oct 01 02:35:19 CDT 2016

Total time taken to generate the page: 0.24064 seconds