No Idea why I can't get string to datetime

ChrisMason

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

MarkSnodgrass

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.

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?

MarkSnodgrass

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.

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?

ChrisMason

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

MarkSnodgrass

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.

ChrisMason

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!