String to Datetime

Question

Hello team,

I am working on a project which requires me to convert data in string format to date format.

Normally alteryx converts '19/06/50' as '2050-06-19' when i use Datetime tool. This is correct.

However, I am encountering weird problems when the string in question is '20/02/60'.

Alteryx converts this to date as '1960-02-20', where as I need '2060-02-20'. 60 is interpreted as 1960, and not as 2060. Same for '22/06/99' interpreted as '1999-06-22' and not as '2099-06-22'.

Why is this and how do I get around this problem ?

Example workflow attached.

Thanks a lot for your help.

Raksha

Alteryx example.yxmd

estherb47 · Accepted Answer

Hi @rakshabhat

I can't speak to the why it's happening, but you could add a formula tool after the datetime tool that checks on the year, and if it's in the 20th century, use DateTimeAdd to add 100 years and make it the year you desire.

e.g., IIF(DateTimeYear([Field1])<2000,DateTimeAdd([Field1],100,
"years"),[Field1])

I'm on my phone so cannot check on your dataset. This will work to convert from 20th to 21st century.

Let me know if that helps.

Cheers!

Esther

echuong1 · Accepted Answer

You can embed logic into a conditional expression in a formula to properly format the dates.

When would be considered the cut off point for 1990's values? As in, what is the oldest value that would have a 19 added to the first part of the year, versus 20?

For example, if we had 19/06/70, should that be 1970 or 2070, and so forth?

Or do you just want all values to be in the 2000's for the year? If that is what you're looking for, you can just append "20" to the year.

pedrodrfaria · Accepted Answer

Hi @rakshabhat

Just add 20 to the front before the conversion and then use the datetime tool