DateTimeParse 2 digit year %y

[Jonny]

Hi Folks,

I need to convert same DB Date to Alteryx Date Format. I am puzzled by DateTimeParse not behaving as I would have expected. so reaching out for help.

My date in DB is like 1210601 - which should read as 2021-06-01

using DateTimeParse in formula as DateTimeParse(Right(ToString([Date despatched]),6),"%y%m%d")

it gives me 2106-01-01 - refer below screen shots.  The weird bit is that although I am using small "y" it still gives me a 4 digit year.

am I doing something wrong or is this a "feature"? 

sample workflow to help ilustrate the problem:

sample workflow attached. 

I know I could use a formula toDate left, right, and what not. But I really like DateTimeParse and want to know where I am getting it wrong.

Thank you!

[atcodedog05 · Accepted solution]

Hi @Jonny 

Add "20" at the beginning of the string to get it working

DateTimeParse("20"+Right(ToString([Date despatched]),6),"%Y%m%d")

Workflow:

Hope this helps : )

[Jonny]

Legend @atcodedog05 ! that will do the trick. Thanks for the fast response!

But I still wonder, the difference between the "Y" and "y" when using DateTimeParse.

should it "y" read the input string as two digit year?

[atcodedog05]

Hi @Jonny 

I was guessing the same. But then I checked the documentation page and this appears to be a limitation.

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

Hope this helps : )

[atcodedog05]

Hi @Jonny 

We can introduce a seperator using Regex and %y will work.

Hope this helps : )