ETL and Data Integration tools - Modern ETL software for everyone

I'm trying to format a date that is being pulled in from a .csv file. The format on the sheet shows the date in the format of "4/12/2009". When I try to transform this date into any other date, it tells me that the that 4/12/2009 is not a valid date format. I'm trying to format it as YYYY/MM/DD to import it into a MySQL database. I'm assuming that because there is only a single character for the date, it is not reading it correctly - and there is no option in the default date settings for D/MM/YYYY - only DD/MM/YYYY. Is there any way around this?

"4/12/2009" is not the same as "04/12/2009"

try D/MM/YYYY

before loading the data into date or time fields it should be converted into YYYY-MM-DD HH:NN: SS.ZZZ format
Than our software can populate date/time fields correctly.

So if your data is already in YYYY-MM-DD HH:NN: SS.ZZZ there is nothing for you to do if not use data transformer + date format function.

Mike

Yes I realize that, but the .csv sheet I'm using has the date in that format (no leading 0) It comes from an external source, so I can't change the format on the .csv before hande unless I was to open the sheet everyday and do it manually (defeats the purpose of automating an ELT). So I'm trying to convert it to YYYYMMDD to import it into a mysql database. However the software doesnt recognize "4/12/2009" as a valid date, so it wont transform it into anything - just changes it to NULL.

I guess I can just change the field in the database to a VARCHAR and import it as text and deal with the date transformation later.

I see
Put a transformer object before your data reader

Open transformer and put Date format function between input and output field
Double click on it and select D/MM/YYYY as format
This should help you

This Data transformation got an example of how you can do it

www.dbsoftlab.com/online-tutorials/advan...o-ms-sql-server.html

Mike