Toad World® Forums

How do I convert the results to 'HH24:MI:SS' format?


#1

I cannot find a way to make the final results appear in a TIMESTAMP format. I would appreciate any help y’all may have.

Select Distinct

b.WH_ID

, b.ORDER_NUMBER

, Avg(b.PICKING_TIME) As Picking_Start_Avg

, Avg(b.STAGING_TIME) As Picking_Completion_Avg

, Avg(b.SHIPPING_TIME) As Birth_2_Death_Avg

From(Select distinct

a.wh_id

, a.WH_TIMEZONE

, a.ORDER_NUMBER

, Sum(a.LINES_PICKED) As Total_Lines_Picked

, Sum(a.LINES_SHIPPED) As Total_Lines_Shipped

, Sum(a.FIRST_PICK-a.EST_PRINT_TIME) As Picking_Time

, Sum(a.STAGED-a.EST_PRINT_TIME) As Staging_Time

, Sum(a.SHIPPED-a.EST_PRINT_TIME) As Shipping_Time

From (SELECT distinct

orm.wh_id, WHS.WH_TIMEZONE, orm.ORDER_NUMBER, orm.CARRIER,

count(distinct tl.line_number) over (partition by tl.control_number) as lines_picked,

count(distinct tl2.line_number) over (partition by tl2.control_number) as lines_shipped,

max(orm.STAGED_DATE) over (partition by orm.wh_id, orm.order_number) as EST_PRINT_TIME,

case when whs.wh_timezone=‘US/Pacific’ then ‘3’ when whs.wh_timezone=‘US/Mountain’ then ‘2’ when whs.wh_timezone=‘US/Central’ then ‘1’ else ‘0’ end as subtr_hours,

orm.earliest_delivery_date,

min(tl.start_tran_time) over (partition by orm.wh_id, orm.order_number) as first_pick,

max(tl.end_tran_time) over (partition by orm.wh_id, orm.order_number) as staged,

max(tl2.end_tran_time) over (partition by orm.wh_id, orm.order_number) as shipped

FROM SI_ORDER ORM

inner join t_whse whs on whs.wh_id=orm.wh_id left

outer join t_tran_log tl on tl.wh_id=orm.wh_id and


#2

TO_CHAR(ColumnName,‘hh24:mi:ss’) I believe is the Oracle function you need. More info can be found here.


#3

Tried that and failed.

Thank you and have a wonderful day,

Ryan A. Jones

**Branch Configuration Analyst
**C:443-801-1974 Direct F: 443-543-2244

www.ferguson.com

www.mirabelleproducts.com

www.monogrambrass.com

www.proflo.com

  [](http://www.facebook.com/FergusonShowrooms)

From: john.mcgraw [mailto:bounce-johnmcgraw@toadworld.com]

Sent: Thursday, June 7, 2018 5:45 PM

To: toaddatapoint@toadworld.com

Subject: [EXT] RE: [Toad Data Point - Discussion Forum] How do I convert the results to ‘HH24:MI:SS’ format?

RE: How do I convert the results to ‘HH24:MI:SS’ format?

Reply by john.mcgraw

TO_CHAR(ColumnName,‘hh24:mi:ss’) I believe is the Oracle function you need. More info can be found
here
.

**John Mc Graw

Data and Reporting Specialist**

P 303-866-6974

1575 Sherman Street, Denver CO 80203

john.mcgraw@state.co.us I www.colorado.gov/cdhs

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.


#4

What was the result? This seems to work fine for me.

SELECT TO_CHAR(SYSDATE + 12/24,‘hh24:mi:ss’) AS CurrentTimePlus12 FROM DUAL


#5

Lookup Error

ORA-01481: invalid number format model

Thank you and have a wonderful day,

Ryan A. Jones

**Branch Configuration Analyst
**C:443-801-1974 Direct F: 443-543-2244

www.ferguson.com

www.mirabelleproducts.com

www.monogrambrass.com

www.proflo.com

  [](http://www.facebook.com/FergusonShowrooms)

From: john.mcgraw [mailto:bounce-johnmcgraw@toadworld.com]

Sent: Friday, June 8, 2018 10:28 AM

To: toaddatapoint@toadworld.com

Subject: [EXT] RE: [Toad Data Point - Discussion Forum] How do I convert the results to ‘HH24:MI:SS’ format?

RE: How do I convert the results to ‘HH24:MI:SS’ format?

Reply by john.mcgraw

What was the result? This seems to work fine for me.

SELECT TO_CHAR(SYSDATE + 12/24,‘hh24:mi:ss’) AS CurrentTimePlus12 FROM DUAL

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.


#6

To get around that try and use TO_DATE() on your incoming date field. So something like…

TO_CHAR(TO_DATE(ColumnName,‘hh:mm:ss’),‘hh24:mi:ss’)

More on TO_DATE() can be found here.