Stacey, you mentioned that this field is defined as a Date/Time field, but your sample data look like this is a string field.

This makes a huge difference in how this field will be handled by Informatica products: if it's a Date/Time field, then Informatica software expects (at least as of my experience) that all records use the same format for the date/time values.

However, in your sample data formats are pretty "dirty"; month and day numbers are shortened to one digit whenever possible. And that's nothing that Informatica software handles very well.

So the first question is: does the source definition contain Date/Time or String fields here?

Second question: if it's a Date/Time field, does the file really contain such "ugly and dirty" date/time values, or are these date/time values from your sample data just displayed in this manner?

So what you need to do is to test the format for each input record and - from a logical point of view - use one or the other or the third or the fourth... mask for the TO_DATE() function to translate this string into a Date/Time value.

Having said that, my approach would be the following:

First I would use the INSTR() and SUBSTR() functions to split up each date/time string into its individual parts. Then examine each part whether it needs padding with leading zeroes.

Finally concatenate all "cleaned-up" parts into one date/time string of a "clean" format and use the TO_DATE() function to translate that string ino a Date/Time value which you can forward to your target(s).

My experience with ICS is VERY limited, but as far as I recall ICS does offer Expression transformations.

That means: what you need to do is to build a "custom mapping" (sorry, off the top of my head I don't recall the real name of such a "manually built mapping") instead of using any of the predefined mapping templates.

Within this "manually built mapping" you have a file source reader which reads the file as a text file; then use one or more Expression transformations to convert/translate those date/time strings into "usable" Date/Time values.