Date Field Conversion Error

[mbarot]

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?

[atcodedog05 · Accepted 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

[AngelosPachis · Accepted solution]

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

[mbarot]

It worked. Thank you so much. 

[mbarot]

My earlier config was custom and I tried keeping it in the mm/dd/yyyy format but this one worked out. Thank you.

[AngelosPachis]

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.

[atcodedog05]

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🙂