DateTime is not compatible with some input setups

[berenyiattila]

Dear Community,

There is some input setup that makes an automatic change in my date that cannot be changed with DateTime or formula.

The raw input is this: 

18/12/2018

But when the Alteryx read the input is has the value of V_String but with this:

2018-12-18

I would like to have the numbers look like this:

18-12-2018

I thought it is an easy step, but turned out that is not. I tried the following:

- set the format to date with Select, and then formula DateTimeFormat with parameters dd-MM-yyyy - result is (Null)

Logically this means that it is not in real date format so I tried to convert first:

- use DateTime to create Date from string with parameters FROM: yyyy-mm-dd - (Null)

It not worked so I used the original format in my raw file:

- use DateTime to create Date from string with parameters FROM: dd/MM/yyyy - (Null)

Now I don't understand why it just not able to change it.

Could you please help me?

Thank you:
Attila

[atcodedog05]

Hi @berenyiattila 

Can you provide an example excel input file which has the issue. That we will be best able to look into it and best provide a solution.

[berenyiattila]

Hi atcodedog05,

Attached is a SAMPLE part of the file.

I have only deleted data from the original and saved under the same format.

Thanks for picking up the issue.

Attila

[Kenda]

Hi @berenyiattila 

According to this article, Alteryx uses ISO format for dates. This means if your date is not in the yyyy-mm-dd format, it will be read as a string. This is likely why you're getting null results. 

In your case, you can still rearrange your date to get it into the format you're looking for, but the field will have to be assigned as a string. Hope this helps!

[berenyiattila]

Hi Kenda,

I come to the same result but still I cannot change the string to date which part is that I don't understand.

Thanks:

Attila

[atcodedog05 · Accepted solution]

Hi @berenyiattila 

Here is how you can do it. Refer to the highlighted configuration. I am using Multi-field to apply on 2 fields and change datatype to string.

Only supported date format is yyyy-mm-dd hence columns needs to be changed to string.

Workflow:

If its formula tool approach first you should change datatype to string using select tool and then DateTimeFormat.

Hope this helps : )

[Luke_C · Accepted solution]

Hi @berenyiattila 

See attached workflow. I took your excel file and converted the fields using the multifield formula tool, although the formula would remain unchanged if you wanted to use it elsewhere. It looks like Alteryx recognized the dates in your input file as dates already, so it's just a matter of formatting the date as a string in your desired format.

datetimeformat([_CurrentField_],'%d-%m-%Y')

[berenyiattila]

Hi @atcodedog05 ,

Hi @Luke_C ,

Thanks for your support and help!

What is the reason I have to use the %?

I had a similar formula, but without %: "dd-MM-yyyy" and it showed (null). I would like understand what was the problem with the exact date format.

Thanks:
Attila

[atcodedog05]

Hi @berenyiattila 

Thats how the function works please refer to the datetime functions documentation page. You should be able to get better understanding 🙂

https://help.alteryx.com/20213/designer/datetime-functions

Hope this helps : )