Interpret and format timestamp field

[pauljohansson]

I have a Timestamp field that i want to interpret and format, but I can get it right.

I want to convert field: 2016-04-04T11:16:44.327

to Timestamp field: 2016-04-04 23:16:44

That is, i want to remove the 'T' and remove the milliseconds. My thinking goes:

=timestamp(timestamp#(replace(timevalue, 'T', ' '), 'YYYY-MM-DD H:MM:SS:FFF TT'), 'YYYY-MM-DD HH:MM:SS')

But it doesnt give any reuslt.  Any pointers?

thanks in advance,
Paul

[sunny_talwar]

May be try this:

=TimeStamp(Floor(Timestamp#('2016-04-04T11:16:44.327', 'YYYY-MM-DDThh:mm:ss.fff'), 1/(24*60*60)))

or this:

=TimeStamp(Floor(Timestamp#('2016-04-04T11:16:44.327', 'YYYY-MM-DDThh:mm:ss.fff'), 1/(24*60*60)), 'YYYY-MM-DD hh:mm:ss')

[Kushal_Chawda]

I am not sure but try

timestamp(frac(timestamp#(purgechar(timevalue, 'T'), 'YYYY-MM-DDhh:mm:ss.[fff]'))*24, 'YYYY-MM-DD hh:mm:ss')

[sunny_talwar]

How do you know if 2016-04-04T11:16:44.327 is AM or PM? Does it have that information at the end?

[sunny_talwar]

I guess this could work:

=TimeStamp(Floor(Timestamp#(PurgeChar('2016-04-04T11:16:44.327 PM', 'T'), 'YYYY-MM-DDhh:mm:ss.fff TT'), 1/(24*60*60)), 'YYYY-MM-DD hh:mm:ss')

[pauljohansson]

my misstake, it is actually 24h, example:

2016-04-04T21:08:57.453

[Kushal_Chawda]

I Updated the expression can you check? I don't have qv

[Kushal_Chawda · Accepted solution]

then this should work

timestamp(timestamp#(purgechar(timevalue, 'T'), 'YYYY-MM-DDhh:mm:ss.[fff]')), 'YYYY-MM-DD hh:mm:ss')

[sunny_talwar]

I think what you had earlier should have worked, but I guess the input just got changed

[sunny_talwar]

This would work:

=TimeStamp(Floor(Timestamp#('2016-04-04T21:08:57.453', 'YYYY-MM-DDThh:mm:ss.fff'), 1/(24*60*60)), 'YYYY-MM-DD hh:mm:ss')