DateTime Parse showing incorrect results

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.

Ateryx DateTime Parse.PNG

Parse

Date Time

Accepted answers

iwobarto

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!

Sample date parsing_multiple formats.yxmd

All comments

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:

Example 1.PNG

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

Example 2.PNG

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

Example 3.PNG

Example 4.PNG

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703