Date reading differently for Jan-Sep than Oct-Dec & breaking flow

[sselby6909]

Good Afternoon, 

I'm working on developing an app that combines two bank download files and then filters data out for a specific date/range using the "Date" interface tool. However, because the interface tool requires the date to be in a specified format, I'm needing to convert the date.

Bank 1 has two different date fields "As of Date" which is a string and has no delimiters (ex 20240930) and a "Value Date" field has delimiters, but drops the leading zero for Jan-Sep. I created a work around that would add back the leading zero and keep the field as "09/30/2024" and then used the String to Date Time format tool.  However, when testing the flow with Oct/Nov data it was found that Alteryx was reading in the "Value Date" as "2024/11/14" and thus the "String to Date Time" tool no longer worked and is throwing the below error. 

When I attempt to use the Date Time Conversion tool on the "As of Date" for Bank 1 under custom since there is no delimiter, it doesn't read the data correctly and the Example noted below does not match the incoming string and the output is not correct either. 

I've included a small data sample and a sample workflow for this. 

[nagakavyasri]

@sselby6909 Change the date format as below:

If no delimiters then,

[sselby6909]

I'm not having issues with Bank 2, just bank 1. Those seem to be reading correctly. 

[nagakavyasri]

@sselby6909 If no delimiters and As-of Date is of format '20241114' then use 'yyyyMMdd' in Datetime tool; if 'Value Date'  is to be considered with format 11/14/2024 then use 'MM/dd/yyyy' in Datetimetool. Check above screenshots for the format.

[sselby6909]

Is the custom not working for me because I'm using lowercase for the mm instead of uppercase MM?

[SPetrie]

Lowercase mm would be the two digit minute, uppercase MM is a two digit month when using the DateTime tool. That will give you errors or produce strange results if you are giving it yyyymmdd as the format.

[The_Rad_Valentina · Accepted solution]

You could also use Formula tool with these formulas in them to convert the dates. 

Bank 1

As of Date

DateTimeParse([As of Date], "%Y%m%d")

Value Date  

DateTimeParse([Value Date], "%m/%d/%Y")

[sselby6909]

Thank you for the explaination. I was not aware of this.