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

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.

Example Flow.png Date Tim Conversion Config.png

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.

Date Time Conversion - Custom.png

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

Community Example - Test.yxzp

Workflow

Date Time

Apps

Help

Dynamic Processing

Interface Tools

Accepted answers

The_Rad_Valentina

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")

All comments

nagakavyasri

@sselby6909 Change the date format as below:

Screenshot 2024-11-15 131651.png

If no delimiters then,

Screenshot 2024-11-15 132148.png

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

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703