text to date conversion

[tww]

Hi all,

I am new to Alteryx, and looking for some help on the conversion of the following text strings to proper date format:

Input	Input format	expected output format
210802	yymmdd	2021/08/02
2021214	yyyyddd (last three digit in Julian format)	2021/08/02
80321	mmddyy	2021/08/03
8022021	mmddyyyy	2021/08/02
21214	yyddd (last three digit in Julian format)	2021/08/02

Also how do I avoid this error "Cannot convert "0" to a date/time"? Should I replace 0 with null?

Thank you in advance for your help

[SPetrie · Accepted solution]

Are all these formats coming to you in one column from the same source, or is this just a grouping of examples formats you need help with?

Individually, these can be tackled with formulas or the date/time tool. If these are coming to you like this, where they are all intermixed, its going to make it a bit trickier to get these to all properly format. 

If the later case is true, I would start by having a conversation with whatever monster decided to allow all those formats in a single column and see if they can clean it up before it gets to you lol.

Individually they can be parsed with these formulas. 

Input	Input format	Alteryx formula
210802	yymmdd	DateTimeParse("20"+[Input],"%y%m%d")
2021214	yyyyddd (last three digit in Julian format)	DateTimeParse([Input],"%Y%j")
80321	mmddyy	DateTimeParse(PadLeft([Input],6,"0"),"%m%d%y")
8022021	mmddyyyy	DateTimeParse(PadLeft([Input],8,"0"),"%m%d%y")
21214	yyddd (last three digit in Julian format)	DateTimeParse("20"+[Input],"%y%j")

The tricky part will be if these are all in one column. A formula to parse one of the date formats may work on the other but give you incorrect information as seen with the formula for the second Julian date.

[tww]

Thank you very much, SPetrie. They were just different formats I got from different fields. So your solution is what I am looking for.

By the way for "210802", what is the reason that this - DateTimeParse([Input],"%y%m%d") would not work?

[Dina]

This is the explanation from Alteryx Documentation.