DateTime tool conversion error
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@arthemaise happy to help! Please remember to mark the solution so it can be easily searched by others in the future.
