String to date

[tahiradeel]

Hello guys

I have a comma separated text file. I want to convert Field_5 to date. I tried formula tool and DateTime tool but could not succeeded. String is actually in yyMMdd format. DateTime tool works only with yy. I tried to use cleaning tool as well to clean the data but no difference. Could anyone suggest a solution? The text file is attached. 

Thanks

[Christina_H · Accepted solution]

That's odd, the date time tool definitely should be able to parse that.  DateTimeParse in a formula tool won't do it either.  I have been able to do it by adding "20" at the start: DateTimeParse("20"+[Field_5],"%y%m%d")

[Christina_H · Accepted solution]

This will be why - it will read 4 digits for the year if there's no separator:

DateTime Functions | Alteryx Help

[caltang · Accepted solution]

Hi @tahiradeel & @Christina_H 

Yes, Alteryx has mentioned this also: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Convert-YYMMDD-number-into-Dat...

Adding the "20" to the start will solve it.

DateTimeParse("20"+[Field_5],"%y%m%d")