Originally posted by: SystemAdmin

I have a string containing a date in the format
SUN OCT 24 13:15:00 BST 2004
and I want to convert it to
2004-10-24 13:15:00

I have tried using todatetime to get it into a date format so that I can convert it back to text in the right format using variants of
TODATETIME(despatchDate:XML_Data_In,"{DAY MON DD HH24:MM:SS BST CCYY}")) but I can't get anything to work. Any help would be appreciated.
Jan
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Intuitively the following should probably work (IMHO) but certain combinations of date formats in certain orders seem to be problematic:
DATETOTEXT(TODATETIME(dateIn,"{DY MON DD HH24:MM:SS ZZZ CCYY}"))

However, the less direct approach that follows appears to work and will at least give you one possible way to do this:
=WORD(dateIn," ",6)+"-"+
FROMDATETIME(TODATETIME(WORD(dateIn," ",2),"{MON}"),"MM")+"-"+
WORD(dateIn," ",3)+" "+
WORD(dateIn," ",4)
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Thanks for that. Option 1 didn't work but option 2 was fine.
Jan
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender