DateTime Parse showing incorrect results

[iwobarto]

Hello,

I have an Excel data set with dates formatted differently within one field. I was trying to use a DateTime Parse tool to parse each of the formats separately (i expected null values for dates formatted differently then the selected match format). However, when I choose the yyyy-MM-dd as the format to be matched in the tool, it also parses a date formatted dd/MM/yyyy (19/12/2018 becomes 2019-12-20 instead of 2018/12/19 or a parse error/null value).

Is there any way to stop this from happening?

Or do I have to use a workaround and a conditional DateTimeParse formula for parsing the date? 

I would appreciate any support.

[Thableaus]

Hi @iwobarto

What is the configuration of the DateTime tool you're using?

I'd recommend to use the DateTimeParse formula, I'm pretty sure it returns Null Values as you try different dates.

[iwobarto]

Hi @Thableaus

I'm using the version 2018.2.6.51223 of Alteryx designer. The thing is that when I use the build-in Datetime Parse tool I get the results like this: 

When I use a formula for the same purpose, the results are the same:

In both cases I get null for '19.12.2018' because it's not recognised. I was wondering if I can configure the tool to show me nulls for all date formats that are not matching the pattern yyyy-MM-dd. So basically I was expecting to get the same result for 19/12/2018 and 19.12.2018. 

I already found a solution. When I use a conditional formula I can get the results I need (and adding additional conditions will parse all of the formats correctly, if needed): 

So my point is I would rather like Alteryx to match the exact incoming date pattern I choose (in this case yyyy-MM-dd) than to "guess" the date based on some other patterns.  I was not able to achieve this using DateTimeParse alone, but I found a workaround.  Thank you for your support!

[Thableaus]

Hey @iwobarto

Have you tried using only a select Tool, to change your String Field to a Date Field?

I can clearly see that your date is already on Alteryx format, so when you use a select Tool to change from String to Date, the Date in the correct format is going to automatically be converted, so no need for parsing.

Let me know if it works well for you.

Cheers,

[iwobarto · Accepted solution]

Hi, 

I wanted to follow up on this old topic 🙂 

What I did is I created a Multi-Field formula, which solved my problem.

IF regex_match([_CurrentField_],'\d{4}\-\d{2}\-\d{2}') then datetimeparse([_CurrentField_],'%Y-%m-%d')
ELSEIF regex_match([_CurrentField_],'\d+\-\d{2}\-\d{4}') then datetimeparse ([_CurrentField_],'%d/%m/%Y')
ELSEIF regex_match([_CurrentField_],'\d{4}\/d{2}\/\d{2}') then datetimeparse([_CurrentField_],'%Y/%m/%d')
ELSEIF regex_match([_CurrentField_],'\d+\/\d{2}\/\d{4}') then datetimeparse([_CurrentField_],'%d/%m/%Y')
ELSEIF regex_match([_CurrentField_],'\d{4}\.d{2}\.\d{2}') then datetimeparse([_CurrentField_],'%Y.%m.%d')
ELSEIF regex_match([_CurrentField_],'\d+\.\d{2}\.\d{4}') then datetimeparse([_CurrentField_],'%d.%m.%Y')
ELSEIF regex_match([_CurrentField_],'\d{5}') then datetimeadd('1900-01-01',tonumber([_CurrentField_])-2,'days')
ELSEIF regex_match([_CurrentField_],'\d+\-.+\-\d{4}') then datetimeparse([_CurrentField_],'%d/%b/%Y')
ELSEIF regex_match([_CurrentField_],'\d+\-.+\-\d{4}') then datetimeparse([_CurrentField_],'%d/%b/%Y')

else [_CurrentField_] endif

I change all field types to string and then use the Multi-Field Formula to parse the dates, This is because I can have multiple date formats in one column.

I continue adding new rules if necessary.

The only thing I am not able to do this way is distinguishing the American date format, as I do not have this information in the original text data source (so I had to assume that 04.05.2019 is the 4th of May not the 5th of April). 

Thank you for your support!