Convert long timestamp incl milliseconds to date/time

[andy-w]

I have a timestamp field that Qlik recognises as string. An example of the structure is:

12-Nov-23 05:26:08 417000 PM

I'd like this to be a datetime field. In data manager I tried the following, but it still loads as a string. Any idea how to have Qlik recognise this as a datetime, and open the different datetime functions on the sheets?

TIMESTAMP#(TIMESTAMP,'DD-MMM-YY hh:mm:ss.ffffff TT')

[rwunderlich]

If that a typo in your format string?  You have "." between ss.fffff but in your data example you show a space.

-Rob

[andy-w]

Good spot, yes, there's a typo. Correcting that, the field does load as datetime, which is great. However, is it possible to load only the year, or month, or hour, or whatever component in a chart? Do I need to create calculated field(s) for this?

I'm very new to Qlik, but very experienced in Tableau, where dropping dateparts into a chart is very simple and doesn't require creating new fields.

[Ahidhar]

try this 

timestamp(TIMESTAMP#(TIMESTAMP,'DD-MMM-YY hh:mm:ss ffffff TT'),'YYYY')

this will display just the year

[steeefan]

It will display the year, but the field still contains the whole timestamp value, i.e. 45242.726486308. If you want the field to just contain the year, use Year(TIMESTAMP#(TIMESTAMP,'DD-MMM-YY hh:mm:ss ffffff TT')).