Home » SQL & PL/SQL » SQL & PL/SQL » sum of time
sum of time [message #424127] Wed, 30 September 2009 05:21 Go to next message
mubeenmoin
Messages: 15
Registered: April 2009
Location: KUWAIT
Junior Member
Dear All,

need help please

want to get sum of time

SELECT SUM((TO_DATE('01012000'||END_TIME,'DDMMYYYYHH24:MI') - TO_DATE('01012000'|| START_TIME,'DDMMYYYYHH24:MI'))* 1440/60)AS TOTAL
FROM PERMISSION

by the code written above i get the sum but its not wat i want
it gives me

TOTAL
---------
10.25

Instead of 10.25 it shud display 10:15 (10 HOURS AND 15 MINS)

Any help, advise would be highly appreciated
Re: sum of time [message #424130 is a reply to message #424127] Wed, 30 September 2009 05:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try
SELECT to_char(trunc(sysdate) + SUM((TO_DATE('01012000'||END_TIME,'DDMMYYYYHH24:MI') - TO_DATE('01012000'|| START_TIME,'DDMMYYYYHH24:MI'))* 1440/60),'HH24:MI') AS TOTAL
FROM PERMISSION


[Updated on: Wed, 30 September 2009 05:29]

Report message to a moderator

Re: sum of time [message #424135 is a reply to message #424127] Wed, 30 September 2009 05:36 Go to previous messageGo to next message
mubeenmoin
Messages: 15
Registered: April 2009
Location: KUWAIT
Junior Member
no its not giving the right output

output should be
TOTAL
-----
10:15
and its giving
TOTAL
-----
06:00
Re: sum of time [message #424137 is a reply to message #424135] Wed, 30 September 2009 05:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you could have been bothered to post a test case, then I'd have bothered testing my solution.

SELECT to_char(trunc(sysdate) + SUM((TO_DATE('01012000'||END_TIME,'DDMMYYYYHH24:MI') - TO_DATE('01012000'|| START_TIME,'DDMMYYYYHH24:MI'))),'HH24:MI') AS TOTAL
FROM PERMISSION
Re: sum of time [message #424138 is a reply to message #424127] Wed, 30 September 2009 05:45 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
SELECT floor(tot_mins/60) AS hours, 
       tot_mins - floor(tot_mins/60) * 60 AS mins
FROM (SELECT (TO_DATE('01012000'||end_time,'DDMMYYYYHH24:MI') 
              - TO_DATE('01012000'||start_time,'DDMMYYYYHH24:MI')
             ) * 24*60 AS tot_mins 
      FROM permission)


I leave it to you to combine the outputs into the format you want.
Re: sum of time [message #424229 is a reply to message #424127] Wed, 30 September 2009 22:25 Go to previous messageGo to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
You might have used REPLACE

For Example

select replace('hi.1','.',':') from dual
Re: sum of time [message #424234 is a reply to message #424138] Thu, 01 October 2009 00:24 Go to previous message
mubeenmoin
Messages: 15
Registered: April 2009
Location: KUWAIT
Junior Member
Thanx cookiemonster

it works
actually i wanted to get the sum of Total Time so heres is what i made change to the statements which u had given


SELECT floor(tot_mins/60) AS hours, tot_mins - floor(tot_mins/60) * 60 AS mins
FROM (SELECT SUM(TO_DATE('01012000'||end_time,'DDMMYYYYHH24:MI')
- TO_DATE('01012000'||start_time,'DDMMYYYYHH24:MI')
) * 24*60 AS tot_mins
FROM permission )
Previous Topic: subscript error
Next Topic: NLS Character Issue
Goto Forum:
  


Current Time: Sun Sep 25 18:03:32 CDT 2016

Total time taken to generate the page: 0.09910 seconds