I have a CSV dataset with a single date column in the mm/dd/yyyy format. When the data is loaded into the designer it is assigned the type - string. I have used the select tool to convert it to date. However, I am running into an error that says Date: "1/15/2015" is not a valid date. And the same errors is shown for a total of 11 dates from the entire dataset.
I have tried using datetime tool as well but that did not work too. What could be the possible solution?
Solved! Go to Solution.
Hi @mbarot
The issue is the date is not in expected Alteryx Date format.
Expected Date Format by Alteryx is yyyy-MM-dd
You can use datetime tool to convert it. Here is a handy resource 🙂
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-DateTime/ta-p/38302
Your expression in datetime tool would be MM/dd/yyyy
Hope this helps 🙂 Feel to ask if you have any questions
Hi @mbarot ,
What's the configuration you've used in the datetime tool? If you configure it as shown below, I think that will solve the issue
Let me know if that worked for you,
Cheers
Angelos
It worked. Thank you so much.
My earlier config was custom and I tried keeping it in the mm/dd/yyyy format but this one worked out. Thank you.
Hi @mbarot ,
Oh I see now, that's because Alteryx translates "mm" as minutes and "MM" as months. You can witness that in the datetime tool configuration window, where time is set as "hh:mm:ss".
That was close enough, a thing to remember when converting date time fields.
Hi @mbarot
Here is the list of Date formats. Its for DateTimeParse() function most of them should give you idea for Datetime tool too.
https://help.alteryx.com/current/designer/datetime-functions
Hope its helpful🙂