topic Re: Convert String to Timestamp in QlikView

Convert String to Timestamp

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a string datetime property of this format: 20181030T133100.000 GMT

and I would like to format it into 30/10/2018 13:31 (DD/MM/YYYY hh:mm).

I've checked this forum: How to convert timestamp to date format

and was able to get my result by:

Date(Date#(SUBFIELD(PROPERTYDATETIME,'T',1),'YYYYMMDD'),'DD/MM/YYYY') &' '& Date(Date#(SUBFIELD(SUBFIELD(PROPERTYDATETIME,'T',2),'.',1),'hhmmss'),'hh:mm')

However, is there a better method of using the TimeStamp function of splitting the property up to format the datetime instead?

Thanks!

Re: Convert String to Timestamp

Anil_Babu_Samineni — Tue, 30 Oct 2018 15:21:40 GMT

Try this?

=Date(Date#(Replace(Field,'T',' '),'YYYYMMDD hhmmss.fff'),'DD/MM/YYYY hh:mm')

=Date(Date#(Replace(SubField(Field, ' ',1),'T',' '),'YYYYMMDD hhmmss.fff'),'DD/MM/YYYY hh:mm')

Re: Convert String to Timestamp

Anonymous — Tue, 30 Oct 2018 15:28:05 GMT

use stringfunction to get rid of text "GMT"

then use

=timestamp(date('20181030T133100.000'),'DD/MM/YYYY hh:mm')

which gives me (you may Format to your dateformat)

Re: Convert String to Timestamp

Anonymous — Wed, 31 Oct 2018 01:42:26 GMT

Thanks!

Ended up with this:

timestamp(date(SUBFIELD(PROPERTYDATETIME,' ',1)),'DD/MM/YYYY hh:mm')

was wondering if I was able to use the formatting to get rid of the gmt... but this works and much neater than mine hahas ^^