Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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#
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
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
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)?
here it is....
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?
QV12 SR1
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