String to Datetime

[rakshabhat]

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

[estherb47 · Accepted solution]

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 solution]

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 solution]

Hi @rakshabhat 

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

[Luke_C]

@rakshabhat 

I agree with other solutions above. As to why this is happening - when parsing years, Alteryx will look at a range of the current year minus 66 to current year plus 33. For example, in 2021, that's 1955 to 2054.

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

[rakshabhat]

Hi all,

Thanks a lot for sharing your responses. All of them work. Now I know the answer to the why as well. 

Alteryx community truly is passionate about helping each other 😊

Thanks again. 

[estherb47]

We are. That's the beauty of community!!

Cheers,

Esther