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#

[agni_gold]

Value which i have provided you at starting of thread is copied from my source excel, and i have rechecked for any white space, no white spaces present.

Thanks for all your inputs , lets to some more hands on on this

[agni_gold]

when using only

Timestamp#(OPENED_AT,'DD-MMM-YY hh:mm:ss.fff TT')  AS Converted

This is making my field but when i used timestamp function then my field is not creating .

Such a weird situation

[swuehl]

Timestamp#(OPENED_AT,'DD-MMM-YY hh:mm:ss.fff TT')  AS Converted

will just return the text value if the conversion fails. If you then apply Timestamp() formatting, it will result to NULL

Could you upload this excel file (or a copy of the field that failed)?

[agni_gold]

here it is....

[swuehl]

Your sample file loaded just fine with this script

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

LOAD OPENED_AT

FROM

[Incident.xlsx]

(ooxml, embedded labels, table is SERVICENOW.INCIDENT);

which version of QV are you using?

[agni_gold]

QV12 SR1

[agni_gold]

Shit man , very silly mistake i made

hh:mm:ss      -----  I am using

But source has

hh.mm.ss     ----- should use

Thanks man all your great help