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#
Timestamp(Timestamp#('29-APR-15 08.47.23.000000000 AM','DD-MMM-YY hh.mm.ss.fff TT'))
seems to work for me.
If it's only about the year, try
=TextBetween('29-APR-15 08.47.23.000000000 AM','-',' ',2)
resp.
TextBetween(TIMESTAMPFIELD,'-',' ',2)
No no actually i want to convert it into timestamp , then date format
Timestamp(Timestamp#('29-APR-15 08.47.23.000000000 AM','DD-MMM-YY hh.mm.ss.fff TT'))
seems to work for me.
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'))
One more thing , but the same thing when i am using in backend script , then it is not working.
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?
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?
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
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)?