No Idea why I can't get string to datetime

ChrisMason
ChrisMason Member
in Magic ETL

Input into formula tool is 2019-01-02 07:30:00 AM, formula is STR_TO_DATE(TimeIn1,'%Y-%m-%d %h:%i:%S %p'). Validate formula checks out, but error is Failed to parse date/time: Error at or near character 11 in string /2019-01-15 7:30:00 AM

Tagged:

Best Answer

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    A good place to try out the function is at W3Schools site.

    https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_str_to_date

    I took your sample date and the format you want and it worked.

    image.png

    This makes me think you have some extra data coming in on some of your rows that is causing the issue. In your error message you have a / . Where is that coming from?

    **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.
  • ChrisMason
    ChrisMason Member

    No Idea where this is coming from. I'm not super great with SQl, and it's my first week with Domo.

  • MarkSnodgrass
    MarkSnodgrass Coach

    Here is one way to troubleshoot. Create a table card and drag in your timein1 field. Drag it in a 2nd time and change the aggregation to count. This will give you a distinct list of date/times in your dataset.

    Next, create a beastmode that looks like this:

    TRY_CAST(timein1 as datetime)

    Drag this field into your table card. Scroll through the list and look for where this field is showing as blank. This means that Domo wasn't able to convert this to a valid date/time. Look at the timein1 field next to it and see what looks odd about it that it can't convert and make any adjustments. As an example, you may need to use a REPLACE function to remove a / if that is in that field.

    **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.
  • ChrisMason
    ChrisMason Member

    Found it. Bad incoming data. On a rare occasion 05:30pm was input as 5:30pm. Messed the whole thing up.Thanks for the assist though!

Categories