Convert string to Date or Timestamp

[rbecher]

Hi,

I want to load a timestamp with this format 'MMM DD hh:mm:ss YYYY' from XML files. I tried a lot of things but cannot make it with the timestamp# or date# functions. What is the most simple way to do this? Why this is a problem?

- Ralf

[disqr_rm]

Any chance you can post an XML sample file here?

[prieper]

Never tried, but this format looks a bit unusual so that there might be some internal logic within the QV-formulae standing against it.
Have you tried to load it as string, then fillet it for each piece and compose a new date?

Peter

[biester]

I tried to read in similar values from a txt file, there e.g. a

date(timestamp#(Timestamp,'MMM DD hh:mm:ss YYYY'),'YYYY.MM.DD hh:mm:ss')

is no problem and the field is correctly evaluated as timestamp.

Should it really be something XML-specific?

Rgds,
Joachim

[rbecher]

This is nothing XML specific. I found out that is has to do with some string operations:

test_xml:
LOAD created_at
, timestamp#(mid(replace(created_at,'+0000 ',''),4),'MMM DD hh:mm:ss YYYY') as badTimestamp
FROM C:\Projekte\Qliktech\#bugs\timeline.xml (XmlSimple, Table is [statuses/status]);

The original format is even more unusual (twitter!): 'Wed Sep 16 16:57:36 +0000 2009'

It will work if I adjust the date in the file to the format 'Sep 16 16:57:36 2009' and strip the string operation in the script. But this is no solution.

- Ralf

[prieper]

Why not? I do not get the point of your complaint?

Peter

[Anonymous · Accepted solution]

Ralf,

This seems to work. I had to add a trim() around the parsed date to get it through.

date#(trim(mid(created_at,4)),'MMM DD hh:mm:ss +0000 YYYY')

[rbecher]

This is a solution but better is:

timestamp#(mid(created_at, 5),'MMM DD hh:mm:ss +0000 YYYY')

This was my fault, but I wonder why there is no wildcard format sign?