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

Date Time

Accepted answers

atcodedog05

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

date format.yxmd

Luke_C

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

New Workflow4.yxmd

All comments

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

Indirect Tax Report_SAMPLE.xlsx

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

Luke_C

Hi @berenyiattila

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

New Workflow4.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335