Convert String to Timestamp on Select

[RobertoMello]

Hello everyone,

I need to load a table which has a time stamp that doesn't match how the it should be formatted.

Also, I want to be able to limit how many days back I load.

This is how the "date" looks like on the MongoDB:

3/1/2020, 12:00:03 AM

And this is the code:

Let vEndDate = text(date((Today()-2), 'YYYY-MM-DD hh:mm:ss'));

LOAD _id,
if([value] > 0.0,[value]) as bat%,
agv as batAGV,
[time] as batTimestamp,
loop as batLoop;

[agvBatteryLevel]:
SELECT [_id],
[value],
agv,
[time],
loop
FROM faults.agvBatteryLevel WHERE ( [time] > '$(vEndDate)' );;

Any help would be appreciated.

Thank you

[anthonyj]

Hi,
Qlik holds timestamps as decimals so for example the num(now( )) function would return 44411.327511574 but displays as 2021-08-03 07:55:18. This is important for your comparison operator ">" so changing it to text means the operator won't work properly.
If it's the [time] column that is in the wrong format and it's not reading correctly then you can format it in the 'where' statement by wrapping it in a Timestamp#( ) function. If it's in the format written above then you'd write it as:
timestamp#([time],'D/M/YYYY, hh:mm:ss tt').
I noticed there's a comma in the format and I'm not sure how the timestamp is going to deal with that but give it a try.
I hope this helps
Regards
Anthony