Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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'))
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'))
'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'
)
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
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.
I use this data in file csv, so i can't upload this for you, sr 😞
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.
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?
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.
Here is file csv.
Thanks for your help.