1

Our Oracle database upgraded from 10g to 12c recently which resulted in a lot of changes to reporting. The below logic used to return data in a 16:25:10 format:

to_char( numtodsinterval(
 (select max(date_entered)
  from co_hist
                where order_no = a.order_no
                and   message_text = 'Delivered')
 -
 (select max(date_entered)
  from co_hist
                where order_no = a.order_no
                and   instr(message_text,'Picklist') != 0
                and   instr(message_text,'picked') != 0), 'DAY') ) elapsed_picked_delivered

After the upgrade, the data returned from the same exact section of logic is: +000000000 16:25:10.000000000

I'm only still needing the 16:25:10. Is there an easier way than using the substr() function to strip the leading/trailing 0s?

KassieB
  • 135
  • 8
  • you can mention the time format as well. like `TO_CHAR(, 'HH:MI:SS');` – Ankit Bajpai Jun 16 '21 at 14:28
  • @AnkitBajpai - that doesn't work for intervals - it's just ignored, isn't it? – Alex Poole Jun 16 '21 at 14:37
  • 1
    I don't recall it looking any different in 10g, but anyway, there are a few ideas [here](https://stackoverflow.com/a/45601629/266304) on how to get the format you want, as a string. You can also do `cast(numtodsinterval(...) as interval day(0) to second(0)` but that still gives you a single digit for the number of days - which could be useful of course; and you could substr that off if not. – Alex Poole Jun 16 '21 at 14:42

1 Answers1

1

I'm only still needing the 16:25:10. Is there an easier way than using the substr() function to strip the leading/trailing 0s?

one of the solutions:

regexp_substr(to_char(numtodsinterval (...your code...,'DAY')),'\d{2}:\d{2}:\d{2}')

N.B. doesn't work for interval types

TO_CHAR(<your_query>, 'HH:MI:SS')
Krasnoslobodtsev Sergei
  • 216
  • 1
  • 5