How to parse 20180619075546.0Z using TimeStamp# function

kevinpintokpa — Sat, 16 Nov 2024 03:26:30 GMT

So I have a string (returned by an Active Directory utility) that looks like this: 20180619075546.0Z

I have tried all manner of format strings with the TimeStamp# function but it simply doesn't work. None of these work, for instance:

Timestamp# ( modifyTimeStamp, 'YYYYMMDDhhmmss.0Z' ) returns 20180619075546.0Z

Timestamp# ( modifyTimeStamp, 'YYYYMMDDhhmmss' ) returns 20180619075546.0Z

Timestamp# ( Left ( modifyTimeStamp, 14 ), 'YYYYMMDDhhmmss' ) returns 20180619075546

Date# ( Left ( modifyTimeStamp, 8 ), 'YYYYMMDD' ) returns 20180619

What I am doing wrong?

Re: How to parse 20180619075546.0Z using TimeStamp# function

kevinpintokpa — Wed, 29 May 2019 00:57:19 GMT

I found that I had to:

a) Trim off any extraneous characters

b) Wrap the expression in another Timestamp function.

So this finally works:

Timestamp ( Timestamp# ( Left ( modifyTimeStamp, 14 ), 'YYYYMMDDhhmmss' ) )

and it properly returns 6/19/2018 7:55:46 AM as desired.