topic Re: Format 2021-05-17T04:00:00.000Z to date in Visualization and Usability

Format 2021-05-17T04:00:00.000Z to date

tuan_nguyen — Thu, 11 Aug 2022 06:57:36 GMT

Hi everyone,
How can i format 2021-05-17T04:00:00.000Z to datetime
I use TextBetween(SubField(Replace(' 2021-05-17T04:00:00.000Z ', '"', ''), 'start_at:', 2), '', ',') but not work

Thanks.

Re: Format 2021-05-17T04:00:00.000Z to date

rubenmarin — Thu, 11 Aug 2022 07:14:13 GMT

Hi, you can use Timestamp#() to specify the input timestamp format:

=Date(Timestamp#('2021-05-17T04:00:00.000Z','YYYY-MM-DDThh:mm:ss.tttZ'))

The previous internally stores the time, just it's not show, to remove the time part:

=Date(Floor(Timestamp#('2021-05-17T04:00:00.000Z','YYYY-MM-DDThh:mm:ss.tttZ')))

Or to keep and also show the time part:

=Timestamp(Timestamp#('2021-05-17T04:00:00.000Z','YYYY-MM-DDThh:mm:ss.tttZ'))

Re: Format 2021-05-17T04:00:00.000Z to date

Øystein_Kolsrud — Thu, 11 Aug 2022 07:14:55 GMT

'T' is a marker for am/pm I believe, so you'll need to get rid of it from the string. Here's an example:

Timestamp#( Replace('2021-05-17T04:00:00.000Z','T',' '), 'YYYY-MM-DD HH:mm:ss.fffZ' )

Re: Format 2021-05-17T04:00:00.000Z to date

tuan_nguyen — Thu, 11 Aug 2022 07:44:27 GMT

Hi @rubenmarin ,
I tried your way, if hard coded like that then the data show up as i want, but if i use variable like in the picture it gets error, can you help me

Re: Format 2021-05-17T04:00:00.000Z to date

rubenmarin — Thu, 11 Aug 2022 07:53:53 GMT

Hi, can you upload a sample app with only that field loaded to make some tests?

Btw, I missed the key and the tttZ should be fffZ, but if you only want to keep the date it should not have any impact.

Re: Format 2021-05-17T04:00:00.000Z to date

tuan_nguyen — Thu, 11 Aug 2022 08:00:16 GMT

I use this data in file csv, so i can't upload this for you, sr 😞

Re: Format 2021-05-17T04:00:00.000Z to date

rubenmarin — Thu, 11 Aug 2022 08:03:39 GMT

I meant to make a new app, just copy the script that loads the datetime field and upload that qvf that only has reservation_start_at field.

This is to create a table like the one you posted before, so I can take a look on the field make some test to conver to date.

Re: Format 2021-05-17T04:00:00.000Z to date

tuan_nguyen — Thu, 11 Aug 2022 08:29:17 GMT

i use qlik in the web, so i don't know how to make a new app, can i upload the file csv for you?

Re: Format 2021-05-17T04:00:00.000Z to date

rubenmarin — Thu, 11 Aug 2022 08:31:58 GMT

Ok, do a copy of the csv and only keep the datetime field. Just because maybe there is some info that it's better to not upload to internet.

Re: Format 2021-05-17T04:00:00.000Z to date

tuan_nguyen — Thu, 11 Aug 2022 08:35:52 GMT

Here is file csv.

Thanks for your help.

Re: Format 2021-05-17T04:00:00.000Z to date

rubenmarin — Thu, 11 Aug 2022 08:43:22 GMT

So the file has uploaded with double quotes at the begining and at the end, I removed them with:

LOAD
Replace([start_at],'"','') as [start_at]

In design I create a table with start_at as dimension and the measure: =Date(Floor(Timestamp#(start_at,'YYYY-MM-DDThh:mm:ss.tttZ')))

I also created it as a dimension, both are working:

If you do the same test with this file it works?

Re: Format 2021-05-17T04:00:00.000Z to date

tuan_nguyen — Thu, 11 Aug 2022 08:54:29 GMT

I just only add more function trim() and it work 😄

Thanks for you help