Issue formatting time field

[mama25macs]

Hello, I am not able to use Time() right now. I have an OrderDateTime field, and I have been able to extract the Date and use Date() so far, but not Time().

I tried the baseline suggestions from QilkSense using these, and neither worked. They did not error out, just did not produce data to use.

Date(Floor(order_datetime)) as OrderDate

Time(Frac(order_datetime)) as OrderTime

I was able to do this for Date() and it works successfully: Date(left(order_datetime,10)) as OrderDate,

Aside from the approach above, here is what else I have tried for Time(), unsuccessfully:

Time(Frac(Right(order_datetime,(len(order_datetime)-11)))) as OrderTime,

Time(Right(order_datetime,(len(order_datetime)-11)))) as OrderTime,

Time(order_datetime) as OrderTime,

etc...

Any thoughts? I have attached the raw data here. 

Thank you!

Michelle

[Kushal_Chawda · Accepted solution]

@mama25macs  try below

time(frac(Timestamp#(SubField( order_datetime,'+'),'YYYY-MM-DD hh:mm:ss.fff'))) as Time

[mama25macs]

That works, thanks! Any chance you can describe that syntax and why in plain english too, and why some of that was required? Thank you!