Failure to parse date/time

LeonN
LeonN Member
in Magic ETL

Any help on this would be appreciated, I can't seem to get rid of this error and I'm not sure what I'm doing wrong or what I need to check.

I'm duplicating an ETL we currently use for a similar dataset, but this dataset is separated by day, rather than month.

Screenshot 2023-09-11 114443.png

Best Answers

  • MichelleH
    MichelleH Coach
    edited September 2023 Answer ✓

    @LeonN The %d specifier in the DATE_FORMAT function is expecting a leading zero in the day, so it is expecting 'Jan 01, 23' instead of 'Jan 1, 23'. You may need to manipulate the field to include a leading zero before the day before using the DATE_FORMAT function. Alternatively you could try using the FUZZY_PARSE_DATE function instead of DATE_FORMAT.

  • marcel_luthi
    marcel_luthi Coach
    edited September 2023 Answer ✓

    You should be able to use %e instead of %d for the day part to account for the day not being provided in the 2 digits format, and remember to use STR_TO_DATE and not the DATE_FORMAT function, as what you have is a string you want to turn into a date and not displaying a date in a specific string format. I just tested my previous suggestion and seems to be working for me:

    STR_TO_DATE(`Year Day Month (text)`,'%b %e, %y')

    I have doubts as in the original post you mentioned having a column called Year Month Date (text) based on your ETL screenshot, but in the table screenshot you're showing the dates to be column names and a column each. What is it that you're trying to achieve? What does your ideal output look like (I doubt you want to have a separate column per each date) and what does your current input look like?

Answers

  • MichelleH
    MichelleH Coach

    @LeonN What do the dates in your data look like?

  • LeonN
    LeonN Member

    Jan 1, 23; Jan 2, 23 etc. (see column names below)

    Screenshot 2023-09-11 123418.png

  • marcel_luthi
    marcel_luthi Coach

    Based on the error in red, it seems your date string includes a comma after the day number and this one is using a single digit version when below day 10, this could be the reason it fails. Try updating your date format to:

    STR_TO_DATE(`Year Day Month (text)`,'%b %e, %y')

  • GrantSmith
    GrantSmith Coach
    edited September 2023

    You need to add a comma to your date format:

    DATE_FORMAT(`MyDateColumn`, '%b %d, %y')

    For others who come across this there's a great Domo KB article on date format string you can find here:

    https://domo-support.domo.com/s/article/360043429953?language=en_US

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • LeonN
    LeonN Member

    I did try adding a comma and I'm still getting the same error.

  • MarkSnodgrass
    MarkSnodgrass Coach

    It also seems like you have some slashes before and after your date. I would try adding a REPLACE function to remove those and then convert to date.

    REPLACE(fieldname,'/','')

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • LeonN
    LeonN Member

    Thanks for the suggestions, but still not having any luck

  • MichelleH
    MichelleH Coach
    edited September 2023 Answer ✓

    @LeonN The %d specifier in the DATE_FORMAT function is expecting a leading zero in the day, so it is expecting 'Jan 01, 23' instead of 'Jan 1, 23'. You may need to manipulate the field to include a leading zero before the day before using the DATE_FORMAT function. Alternatively you could try using the FUZZY_PARSE_DATE function instead of DATE_FORMAT.

  • marcel_luthi
    marcel_luthi Coach
    edited September 2023 Answer ✓

    You should be able to use %e instead of %d for the day part to account for the day not being provided in the 2 digits format, and remember to use STR_TO_DATE and not the DATE_FORMAT function, as what you have is a string you want to turn into a date and not displaying a date in a specific string format. I just tested my previous suggestion and seems to be working for me:

    STR_TO_DATE(`Year Day Month (text)`,'%b %e, %y')

    I have doubts as in the original post you mentioned having a column called Year Month Date (text) based on your ETL screenshot, but in the table screenshot you're showing the dates to be column names and a column each. What is it that you're trying to achieve? What does your ideal output look like (I doubt you want to have a separate column per each date) and what does your current input look like?

Categories