How to convert Date from yyyy-mm-dd to mm/dd/yy
We have a date field coming in from XML file in the form of yyyy-mm-dd and need to reformat it to mm/dd/yy. The built in date formatter does not seem to handle this. Anyone know of a solution?
Are you wanting to store the date field in a DB, or output it to a text file (or string field in a DB)?
If you're putting it in a DB, then don't worry about it. It's parsing the date, and storing the value as a date object. Just put the date object into the DB and it will be fine.
If you're outputting to a file, then in the File output box (Excel, or Text), in the fields tab, format the date the way you want.
If you're outputting to a string field in a DB, then you need to use the select values step to change the meta data to a string, and format it when you're converting it to a string.
Thank you for the response. Putting the data in an excel cell. The built-in formater did NOT work so we wrote a little java to parse it....
var enteredwrkYY = substr(EnteredDate,2,2); //parse year
var enteredwrkMM = substr(EnteredDate,5,2); //parse month
var enteredwrkDD = substr(EnteredDate,8,2); //parse day
var newEnteredDate = enteredwrkMM + '/' + enteredwrkDD + '/' + enteredwrkYY;
No need to use java to parse it - PDI will already do that.
Tell your XML Input that the field is a Date with format yyyy-MM-dd (Case *IS* important here)
Tell your Excel output that the field is a Date with format MM/dd/yy (Case *IS* important here)
Thank you SOoooo much for clarifying the syntax. Works like a charm!