cancel
Showing results for 
Search instead for 
Did you mean: 

Time formatting

viveknarayananp
Meteoroid

Hi ,
i have a datat input which has a 'time' field . It is of type Time.

08:20:00
18:53:00
00:10:00

 

I am trying to get only the "hour"
using : DateTimeFormat([txn_tm],"%H") in the formula tool.

It errors out saying : " Select (17) txn_tm: "08:20:00" is not a valid DateTime" ?

Any idea why? i CAN change nit to string and use substring, but I want to avoid using it.

 

EDIT. I have even tried using DateTimeTrim([txn_tm],"hour"), but I still get it as not a valid time / datetime

 

 

Alteryx Certified Partner
Alteryx Certified Partner

The DATETIMEFORMAT() function requires a Date or a DateTime field.

 

I would suggest adding a field via Formula tool called something like [txn_tm_DT] as a DateTime field:

'1900-01-01 ' + TOSTRING([txn_tm])

 

The date is added just as a dummy.

 

Then add a field via your Formula called something like [Hour] which should be a string:


DATETIMEFORMAT([txn_tm_DT], "%H")

 

I've attached a module as an example.

Kanderson
Alteryx Certified Partner

Great and simple way to get this done. Can also use the DateTime parsing tool to put the results back in a time format. Thank you!

I like @michael_treadwell solution as very simple.

 

One alternative is to use a Regex Tool in parse mode:

timeParse.jpg

 

This will split straight into Hour Minute and Second as fields

 

 

Another workaround using Text To Columns tool and split the text into columns 

Timeparse.PNG

MD2050
Asteroid

Hello-

Is there a way to club hours and minutes together ? meaning 8.20 or 18.53 in a numbers format so that i can add them and get a cumulative of 17.13 hrs?