Date Conversion Issue

[Shaaz]

Hi All,

I'm facing an issue with date conversion using datetime tool.

I've data like below with a single column date and I'm using DateTime tool to convert it to proper date format with %Y-%m-%d but it is giving incorrect output.

I would like to get an error in case input string is not in %Y-%m-%d, like convert this to Null instead of converting this to incorrect date.

Scenario1:

Input:

Test

23-08-2022

Alteryx tool's output:

DateTime_Out

2023-08-20

Expected Output:

DatTime_Out

Null()

Scenario2:

Input:

Test

12-23-2022

Alteryx tool's output:

DateTime_Out

Null()

Here Scenario2 is working fine where DatTimeOut is coming as Null

[Ladarthure]

Hi @Shaaz,

in the datetime tool, when you select string to date, you have to give alteryx the expected input format.

In you case :

23-08-2022 will be dd-mm-YYYY

12-23-2022 will be mm-dd-YYYY

What is happening when you code like you did is that alteryx recognize 23 as a year for 2023 also because the month in the middle of you string is inferior to 12 in the first one and superior to 12 in the second one!

If you data has multiple date time formats in the same column, you might want to identify which format it is before transforming it to a date.

I hope it helps.

[Mahadeva]

Hi @Ladarthure  , thanks for your quick reply.

Referring and adding to @Shaaz 's post,

Our goal is to automate validation of the actual input date string format coming in a column of .csv file against the expected date string format '%Y-%m-%d'.

Hence we used the DateTime component and the mentioned settings expecting that the output would be null() if the input date string doesn't come in the expected '%Y-%m-%d' format and have a customized error message in the further steps.

However, we see there are few date scenario where the output isn't null() even when the input date string format isn't '%Y-%m-%d' as a whole.

Are there any generic approach or automated way to achieve our requirement of validating the input date string format against the expected format that can be passed as a control parameter dynamically to this DateTime tool.

Thanks in advance.

[Ladarthure]

If I were you I would either use already configured formats or type it as dd-MM-yyyy (or the other way around depending on what you can expect) MM-dd-yyyy.

The only problem wich will be complicated to handle would be for a date like 02-02-2022 where both first part and second part could be either month or day.

[Mahadeva]

@Ladarthure Even if I use predefined date formats the values are wrongly converted. As the expected format is yyyy-mm-dd, the workflow is built and migrated to production with the same, and when the input date format in production is different (eg. dd-mm-yyyy) it wrongly converts without us knowing and cannot be modified in production.

[Christina_H · Accepted solution]

How about adding a formula tool after the datetime tool to clear the incorrectly converted dates?

if REGEX_Match([Date],'\d{2,4}-\d{2}-\d{2}') then [DateTime_Out] else Null() endif

[Mahadeva]

@Christina_H  Appreciate your quick reply, this looks like a promising solution, thanks a lot.