Error with String to Date

As shown in the error, there is a value '/3/31/24/' that is not in a date format or one that can be converted to a date. Either fix the value in the source data, or use a formula tile in Magic ETL to convert that value to '03/31/24' then use the alter columns tile to convert the string column to date.

@s_f_OTT I'd suggest checking Format argument of your STR_TO_DATE function. Based on your screenshots, the formula should look like this:

STR_TO_DATE(`dateField`,'%c/%d/%y')

Can you run a preview in your ETL and show the error you get there?

In that error message, the slashes are not part of the problematic value. They are placed around the value by the error message, the way quotes usually would be. They are very confusing and I am going to replace them with quotes.

My guess is the date format you want is '%c/%e/%y', like so:

STR_TO_DATE(dateField, '%c/%e/%y')

When you can't guarantee that your months and days are zero-padded (e.g. 01/01/24, never 1/1/24), you should use %c for months and %e for days. These are forgiving, and will parse the value whether it is zero-padded or not. Their padded alternatives, %m and %d, are not forgiving. They will fail if the value is not padded. This makes them useful for parsing un-delimited formats like 010124 ('%m%d%y'). If you have a delimited format and there is any doubt at all about zero-padding, use %c and %e.

@ggenovese never heard of FUZZY_PARSE_DATE(), super helpful function!!!