Year from Timestamp which is in text format

[agni_gold]

I have below value in excel in text format need to extract year

29-APR-15 08.47.23.000000000 AM

i have tried many ways using timestamp#

[swuehl]

If it's only about the year, try

=TextBetween('29-APR-15 08.47.23.000000000 AM','-',' ',2)

resp.

   TextBetween(TIMESTAMPFIELD,'-',' ',2)

[agni_gold]

No no actually i want to convert it into timestamp , then date format

[swuehl · Accepted solution]

Timestamp(Timestamp#('29-APR-15 08.47.23.000000000 AM','DD-MMM-YY hh.mm.ss.fff TT'))

seems to work for me.

[agni_gold]

Thanks found my mistake

I was making below (in bold) mistake

Timestamp(Timestamp#('29-APR-15 08.47.23.000000000 AM','DD-MMM-YY hh.mm.ss.[fff] TT'))

[agni_gold]

One more thing , but the same thing when i am using in backend script , then it is not working.

[swuehl]

It's even working with your format code at my site (which I would also expect).

Maybe you have also used e.g. a colon instead of dots as separator between hours, minutes, seconds?

[swuehl]

Agnivesh Kumar wrote:

One more thing , but the same thing when i am using in backend script , then it is not working.

Works for me also in the script:

LOAD *,Timestamp(Timestamp#(TIMESTAMP,'DD-MMM-YY hh.mm.ss.[fff] TT')) as Converted

INLINE [

TIMESTAMP

29-APR-15 08.47.23.000000000 AM

];

Could you test if this script is working for you?

[agni_gold]

Your script is also working for me , but in my script i am also using same

Timestamp(Timestamp#(OPENED_AT,'DD-MMM-YY hh:mm:ss.fff TT'))  AS [Incident Open Date],

But it is not working

[swuehl]

Then I think your input field format differs.

Are you sure that the format exactely matches, even for white spaces (there are different kind of white spaces)?