DateTime tool conversion error

[arthemaise]

I have a large set of data which contains the DOBs of individuals. The date format of these columns were in 01-JAN-95 (example). I've used the DateTime tool to convert these dates to the standard yyyy-mm-dd format.

However the issue arises for individuals that are born before 1950, which the tool seems to be incorrectly converting the DOBs from say 11-MAY-40 to 2040-05-11. From a cursory glance of the output, it seems like the dates that are being converted incorrectly are those before 1954.

Can anyone help with solving this problem? 

[Luke_C]

Hi @arthemaise 

This is how alteryx reads 2 digit years. It supports current year - 66, so 1954 sounds about right. One option you could use is a formula to update the years to be 4 digits to be parsed more accurately. Something like the below might work. 

IF ToNumber(Right([DateField],2)) >=21
Then Replace([DateField],Right([DateField],2),'19'+Right([DateField],2))
Else Replace([DateField],Right([DateField],2),'20'+Right([DateField],2))
Endif

DateTime Functions | Alteryx Help

[Luke_C · Accepted solution]

A simpler option might be to just subtract 100 from the year if its > 2021

IF Datetimeyear([DateTime_Out])>2021
then datetimeadd([DateTime_Out],-100,'year')
else [DateTime_Out]
endif

[arthemaise]

The second solution worked perfectly and also a very elegant method of solving the issue.

I initially tried using method 1 (by converting yy to yyyy) but some of the dates weren't being converted correctly - 01-Jan-01 for some reason became 2001-Jan-2001.

[Luke_C]

@arthemaise happy to help! Please remember to mark the solution so it can be easily searched by others in the future.