Datetime output is different from the datetime input

[Cody2020]

Hello hello,

I am currently working on a dataset from kaggle (trending youtube video statistics) and using Alteryx for the cleaning part.

However I have some issues with datetime: the output is different from the input !

WHAT I WANT TO DO ? (expectations)

To change the current format yy.dd.mm (17.14.11) to yyyy-mm-dd (2017-11-14)

WHAT DO I GET ? (results)

- using datetime : yyyy-mm-dd HOWEVER (!) the months do not match, instead of November (11) I get January (01)

- using select: to convert the data type to DATE (since it is a string) but it returns null values.

- using formula: to change data type and/or parse, but again, I only get null values.

Please, help me figure out a way to fix this, I would really appreciate!

I have attached my workflow if you are willing to take a look!

Cody the Newbie

[TrevorS]

Hello @Cody2020 

The Date Time tool can be a bit difficult to understand at times as there are so many ways to go about how to format your date information.

As for your provided format 17.14.11, you would want to use either "yy.dd.MM" OR "%y.dd.MM"
Each one of these should work to translate your date format into the Alteryx Standard format which would output as "2017-11-14" as you are trying to get!

I hope this helps you out!
For further info on the DateTime tool and the different formats you can use, please check out the resources below:
DateTime Tool Help

Tool Mastery DateTime

EDIT: @christine_assaad pointed out that MM needs to be used, not mm (minutes) good catch!

Thanks again!
TrevorS

[christine_assaad]

Hi @Cody2020 

Please try yy.dd.MM - See below. Hope it helps. Cheers!

[JoeS]

Hi @Cody2020 

The problem you are facing is that Alteryx doesn't deal with 2 byte years in a way you would expect.

So what you would want to do is parse the date in the formula tool with this expression:

DateTimeParse("20"+[trending_date],"%Y.%d.%m")

In the formula you can see I hardcoded "20" on the front. this may need to be a bit more complex if you do have dates from 19XX.

So you could then use:

DateTimeParse(Substring([trending_date],3,2)+Right([trending_date],2)+Left([trending_date],2),"%d%m%y")

This would use the calculation mentioned in the screenshot in the help above to figure out if its a 19XX or 20XX date.

Edit: Good spot @christine_assaad  it was the lower case mm that was being used that was the issue as this is minutes not month. 

Leaving the above in in case it helps anyone else where they have a 2 byte year without a separator.