Error with String to Date

s_f_OTT

I am having an issue with a column that looks like a date but for some reason will not convert to a date.

I am trying to use String to Date but it keeps giving me an error. Any ideas?

ggenovese

there's a FUZZY_PARSE_DATE() function in Magic ETL, I tested with the string '/3/31/24/' and it converted it to date without an error

ColemenWilson

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 have checked through my dataset and there is nothing that starts with a '/'

MichelleH

@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')

s_f_OTT

@MichelleH Unfortunately, that did not work either.

Should I split the numbers apart then put them back together?

ColemenWilson

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

ggenovese

there's a FUZZY_PARSE_DATE() function in Magic ETL, I tested with the string '/3/31/24/' and it converted it to date without an error

rco

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.

s_f_OTT

Thanks everyone!!!

Fuzzy_parse_date worked perfectly!

ColemenWilson

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