Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
FRIDAY Deadline | Submit your Alteryx Use Case by August 17th to qualify for this round of Analytics Excellence Awards presented at Inspire Europe 2018! Learn more.

Time formatting


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



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:



I've attached a module as an example.

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:



This will split straight into Hour Minute and Second as fields



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




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?