| adding two numbers to get hours:minutes format [message #464817] |
Fri, 09 July 2010 11:40  |
mohaninforakle
Messages: 55 Registered: January 2009
|
Member |
|
|
Dear friend,
I have a query to add two numbers and get results in hours:minutes format.Example I want to add 12.20 and 6.15 and get result in hours and minutes like 18.35 (hours & minutes).if minutes that is after precision exceed more than 60 it should treat as 1 hour.like i want to add
12.35 (number 1) before precision its hour and after its minutes
06.25 (number 2)
04.25 (number 3)
-----
23.25 (23 hours and 25 minutes)
-----
Please help me on this to solve my problem.Am waiting for reply from you all.Thanks in advance.
|
|
|
|
|
|
|
|
|
|
| Re: adding two numbers to get hours:minutes format [message #464839 is a reply to message #464821] |
Fri, 09 July 2010 17:31   |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
SQL> -- <= 24hrs
SQL> with
2 data as (
3 select 12.20 val from dual
4 union all
5 select 6.15 from dual
6 ),
7 total as (
8 select sum(60*trunc(val)+100*mod(val,1)) total
9 from data
10 )
11 select trunc(total/60)+mod(total,60)/100 result
12 from total;
RESULT
----------
18.35
SQL> -- more than 24hrs OK?
SQL> with
2 data as (
3 select 22.20 val from dual
4 union all
5 select 6.15 from dual
6 ),
7 total as (
8 select sum(60*trunc(val)+100*mod(val,1)) total
9 from data
10 )
11 select trunc(total/60)+mod(total,60)/100 result
12 from total;
RESULT
----------
28.35
SQL> -- using day fractions and dates handles >= 24hrs
SQL> with X as (select ((to_date('22.20', 'hh24.mi')-to_date('00:00','hh24.mi'))+
2 (to_date( '6.15', 'hh24.mi')-to_date('00:00','hh24.mi'))
3 ) dy_fract from dual)
4 select TO_CHAR (TRUNC (dy_fract)) || ' day ' ||
5 TO_CHAR (TRUNC (SYSDATE) + MOD (dy_fract, 1), 'HH24:MI') from X;
TO_CHAR(TRUNC(DY_FRACT))||'DAY'||TO_CHAR(TRUNC(SYS
--------------------------------------------------
1 day 04:35
SQL> -- or using Michel's more fancy format...
SQL> with X as (
2 select '22.20' val from dual
3 union all
4 select '6.15' from dual
5 ),
6 Y as (select sum((to_date(val, 'hh24.mi')-to_date('00:00','hh24.mi'))) tot
7 from X
8 )
9 select TO_CHAR (TRUNC (tot)) || ' day ' ||
10 TO_CHAR (TRUNC (SYSDATE) + MOD (tot, 1), 'HH24:MI') from Y;
TO_CHAR(TRUNC(TOT))||'DAY'||TO_CHAR(TRUNC(SYSDATE)
--------------------------------------------------
1 day 04:35
SQL>
|
|
|
|
|
|
|
|