HELP | Convert unusual string into Date, and Time

[#1]

Looked at lots of posts and am yet unable to convert the following into Date and Time fields.

Field is called LASTORDERDATE

Example value is: 01-APR-02 01.10.36.000000000 PM

I need to convert to

Date 04-01-2012

Time: 01:10:36 PM (or 13:10:36)

Any help is appreciated.

[nagaiank · Accepted solution]

Try the following expressions:

Date(Date#('01-APR-02 01.10.36.000000000 PM','DD-MMM-YY hh.mm.ss.fffffffff TT'),'MM-DD-YYYY')

Date(Date#('01-APR-02 01.10.36.000000000 PM','DD-MMM-YY hh.mm.ss.fffffffff TT'),'hh:mm:ss')

[#3]

Thanks NagaianK

Worked like a charm.