0

I am trying to output the "Duration" time to display in 'days hours:min:sec' and not 'days hours:min:sec:ms'. I want to get rid of the milliseconds in the output. Here is my code so far with the output:

select   to_char("ALERT"."START", 'DD-Mon-YYYY HH24:MI:SS') as "START",
"ALERT"."END_TIME" - "ALERT"."START" as "DURATION",
     "ALERT"."EVENT" as "EVENT",
     "ALERT"."DESC" as "DESC",
   "ALERT"."ACK" as "NOTES"
 from   "TABLE"."COMP" "COMP",
    "TABLE"."ALERT" "ALERT"
 where   "ALERT"."CUSTOMER" in('abc123')
 and     "ALERT"."COMP_ID"="COMP"."ID"
 and     "COMP"."COMP_ID" = 'abc'
 and     "START" >= ('01-JAN-17') and "START" < ('22-MAR-17');

and here is the current output:

 START                         DURATION    EVENT     DESC             NOTES  
----------------------------- ----------- ---------- --------------   ------
20-Mar-2017 05:15:44          0 5:6:59.95 Some Event Some description NA

I would like the "DURATION" column to ouput like (i.e., '8d 5:32:22') instead of (i.e., 8d 5:32:22:956243).

Any help would be greatly appreciated.

Thanks.

PowerUp
  • 55
  • 1
  • 9
  • this should help you http://stackoverflow.com/questions/2246655/omitting-the-milliseconds-in-a-date – haku Mar 27 '17 at 18:46
  • @NoSaidTheCompiler Oracle and SQL Server handle timestamps completely differently so, unfortuantely, that link is useless for an Oracle solution. – MT0 Mar 27 '17 at 18:59

0 Answers0