942425 wrote:Always post the database version and all related script. for example create table insert data statement.
result expected = 14:40 or (if AM/PM so result is ???)
how can i calculate the sum of time ???
please give me some fast tricks of handling date/time format in oracle....
Hope this helps
SELECT SUM(NVL(ROUND(TRUNC(DAC_OT_HOUR)+((TO_NUMBER (SUBSTR (DAC_OT_HOUR, INSTR (DAC_OT_HOUR,'.',1,1)+1)))/60)),0)) FROM DA_CHECK
1 with t(id, tm) 2 as ( select 1,to_date('01.20','hh.mi') from dual 3 union all select 2,to_date('02.30','hh.mi') from dual 4 union all select 3,to_date('04.20','hh.mi') from dual 5 union all select 4,to_date('05.30','hh.mi') from dual 6 union all select 5,to_date('01.00','hh.mi') from dual 7 ) 8 select TO_CHAR(trunc(sysdate,'dd') + sum(TO_DATE(TO_CHAR(tm,'hh24:mi'),'hh24:mi')-trunc(sysdate,'mm')),'hh24:mi') 9* from t / TO_CH _____ 14:40
942425 wrote:It doesn't look like DATE is proper datatype to use. I'd use INTERVAL:
i want to store time in date format column.
with t as ( select 1 id,1.20 time_col from dual union all select 2,2.30 from dual union all select 3,4.20 from dual union all select 4,5.30 from dual union all select 5,1.00 from dual ) select numtodsinterval(sum(trunc(time_col) * 60 + 100 * mod(time_col,1)),'minute') total_time from t / TOTAL_TIME ----------------------------- +000000000 14:40:00.000000000 SQL>
942425 wrote:The total of time is not the same as a point in time. DATE datatype stores a point in time.
i want to store time in date format column.so how can i calculate
the total of time in HH:MI format like the above example (please replace '.' to ':')
oracle version -10.2.0.4.0
942425 wrote:And you just made my point. 21 Feb + 3 March, with some weird interpretation of date arithmetic, could be construed to be 10 days, but 10 days is not a date and cannot be stored in a DATE datatype.
ans1:-10 days (not including time)