How to Convert and Sort Time Format

[poles1c]

Hi,

I have never seen the below time format:

12:47:33.1433286 PM

Does anyone know the name of this time format type and if it came as string, how to convert it to a time format that I could sort on in Alteryx?

Thanks!

Courtney 

[Joe_Mako]

How about an expression like:

DateTimeParse(REGEX_Replace([Time], "(.*)\..*(\u{2})", "$1 $2"),"%I:%M:%S %p")

Here is a break down of the Regex:

(.*)    First capture group that will grab any character any number of times

\.      Escaped period character

.*      Any character any number of times

(\u{2}) Second capture group that is an uppercase letter 2 times

"$1 $2" will concatenate the the first and second capture group with a space between them

[Inactive User · Accepted solution]

Alteryx can only recognize time to the second. To do that, Left([Time],8) will give you 12:47:33 and then Right([Time],2) for PM or AM. Formula:

Left([Time],8)+' '+Right([Time],2)

[poles1c]

Thank you! This was very helpful. The only thing I would add is that I had to pad times with single hours (e.g. 1:00 pm) with a 0 (using padleft()) and that I needed to omit the AM/PM in order to convert to time and sort properly. Thanks!