How to convert strings like 10:00am or 10am to timestamp in Qlik Sense?

[yifan_wu]

Hi,

I have two fields,One is a datetime field that stores the date value and one is a varchar, that stores the time value. But since it is a varchar, there are values like 10am, 10:00am, 10:00 am and 10..Is there anyway I can convert this varchar value to a timestamp and the combine it with the date field? My ultimate goal is to have a field with the actual date time and one with the date time value in a different timezone based on state value.

Thank you!

[Nicole-Smith · Accepted solution]

You can try formatting the timestamp in multiple ways within the alt() function, which will choose whatever returns a numeric value.  So for the examples you gave:

LOAD * INLINE [
Time
10am
10:00am
10:00 am
10
];

You can use:

alt(
timestamp#(Time, 'hhTT'),
timestamp#(Time, 'hh:mmTT'),
timestamp#(Time, 'hh:mm TT'),
timestamp#(Time, 'hh')
)

This will convert the time to a numeric value, which you can then combine with your date field.

[yifan_wu]

Thanks Nicole! I had to add a timestamp() before the alt to make it work