CSV date format is *M/DD/YYYY but Alteryx reading as YYYY-MM-DD

[Jake5]

Hello, 

My workflow is trying to validate that a processor is entering date values in *M/DD/YYYY format.  While doing a test run, I noticed the input file had the correct format entered as shown per the attached, but Alteryx is converting to YYYY-MM-DD.  As a result, my filter is directing these records into the false lane when it should be the true lane

(using custom filter formula !IsNull(DateTimeParse([DATE_SHOULD_PROCESS],'%m/%d/%Y')) . 

Even more confusing is I have another test file - also correctly formated as *M/DD/YYYY and Alteryx is retaining that format as desired.  How can I get Alteryx to be consistent in this regard so as to avoid reporting false errors?

[Luke_C]

Hi @Jake5 

Alteryx's date format is yyyy-mm-dd. Any other format is considered a string. When you load data from excel in if there's a date field identified (i.e. formatted as a date like your screenshot) it will treat it as a date automatically in Alteryx. You could convert back using a datetimeformat formula.

I would note, when you load data from CSVs every field is treated as a string by default, so the real question is are you dealing with Excel or CSVs? If CSVs like your title indicates you should not have this problem.

[Jake5]

I can confirm this is a CSV input I'm using exclusively - and agree, all fields are coming in as V_String.  Additional research has uncovered the following.  If I place the "problem" csv in as an input without opening beforehand, Alteryx reads it in as YYYY-MM-DD.  But if I open the csv and hit save - with no alterations made - then Alteryx will read it in as *M/DD/YYYY.  Any idea why this is happening?  

[Luke_C]

Hi @Jake5 

If you open the csv in notepad or some other text editor what does the data look like? I would bet it is in yyyy-mm-dd format, and opening in Excel and saving retains the format that excel applies.

[Jake5]

yes - I just confirmed in notepad it shows as yyyy-mm-dd format.  So my question would be if I want to consider either *M/DD/YYYY or YYYY-MM-DD as valid formats, how do I write the custom filter to direct either of those conditions to the True lane and anything else to the False lane?  

[binuacs · Accepted solution]

@Jake5 You can check the dates based on / or - and filter it out

[Jake5]

Thank you!  That helped.