Error with String to Date

s_f_OTT
s_f_OTT Member
in Magic ETL

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?

image.png
image.png

Best Answer

  • ggenovese
    ggenovese Contributor
    Answer ✓

    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

Answers

  • ColemenWilson
    ColemenWilson Coach

    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.

    If I solved your problem, please select "yes" above

  • s_f_OTT
    s_f_OTT Member

    I have checked through my dataset and there is nothing that starts with a '/'

  • MichelleH
    MichelleH Coach

    @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
    s_f_OTT Member

    @MichelleH Unfortunately, that did not work either.

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

  • ColemenWilson
    ColemenWilson Coach

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

    If I solved your problem, please select "yes" above

  • ggenovese
    ggenovese Contributor
    Answer ✓

    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
    rco Domo Employee

    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.

    Randall Oveson <randall.oveson@domo.com>

  • s_f_OTT
    s_f_OTT Member

    Thanks everyone!!!

    Fuzzy_parse_date worked perfectly!

  • ColemenWilson
    ColemenWilson Coach

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

    If I solved your problem, please select "yes" above

Categories